https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/106390&feed=atom&action=history
Microsoft KB Archive/106390 - Revision history
2024-03-29T12:46:24Z
Revision history for this page on the wiki
MediaWiki 1.39.3
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/106390&diff=263872&oldid=prev
X010: Text replacement - """ to """
2020-07-20T09:10:02Z
<p>Text replacement - """ to """</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 09:10, 20 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l77">Line 77:</td>
<td colspan="2" class="diff-lineno">Line 77:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> Application.ScreenUpdating = False</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> Application.ScreenUpdating = False</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Unprotect the worksheet to allow editing</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Unprotect the worksheet to allow editing</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div> ActiveSheet.Unprotect (<del style="font-weight: bold; text-decoration: none;">&quot;</del>my_password<del style="font-weight: bold; text-decoration: none;">&quot;</del>)</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div> ActiveSheet.Unprotect (<ins style="font-weight: bold; text-decoration: none;">"</ins>my_password<ins style="font-weight: bold; text-decoration: none;">"</ins>)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Set lastcell to point to the last cell on the sheet</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Set lastcell to point to the last cell on the sheet</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> Set lastcell = Selection.SpecialCells(xlLastCell)</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> Set lastcell = Selection.SpecialCells(xlLastCell)</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l83">Line 83:</td>
<td colspan="2" class="diff-lineno">Line 83:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Place bottom borders on unlocked cells and remove any bottom</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Place bottom borders on unlocked cells and remove any bottom</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'borders from locked cells</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'borders from locked cells</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div> For Each x In Range(<del style="font-weight: bold; text-decoration: none;">&quot;</del>A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>, lastcell)</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div> For Each x In Range(<ins style="font-weight: bold; text-decoration: none;">"</ins>A1<ins style="font-weight: bold; text-decoration: none;">"</ins>, lastcell)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> With x.Borders(xlBottom)</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> With x.Borders(xlBottom)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> If x.Locked = False Then</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> If x.Locked = False Then</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l95">Line 95:</td>
<td colspan="2" class="diff-lineno">Line 95:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Re-apply worksheet protection.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> 'Re-apply worksheet protection.</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div> ActiveSheet.Protect (<del style="font-weight: bold; text-decoration: none;">&quot;</del>my_password<del style="font-weight: bold; text-decoration: none;">&quot;</del>)</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div> ActiveSheet.Protect (<ins style="font-weight: bold; text-decoration: none;">"</ins>my_password<ins style="font-weight: bold; text-decoration: none;">"</ins>)</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> End Sub</pre></li></ol></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> End Sub</pre></li></ol></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>'''NOTE''': If you want to format a specific cell range, replace Range(<del style="font-weight: bold; text-decoration: none;">&quot;</del>A1<del style="font-weight: bold; text-decoration: none;">&quot;</del>, lastcell) with the range you want to format, for example, Range(<del style="font-weight: bold; text-decoration: none;">&quot;</del>A1:G100<del style="font-weight: bold; text-decoration: none;">&quot;</del>).<br /></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>'''NOTE''': If you want to format a specific cell range, replace Range(<ins style="font-weight: bold; text-decoration: none;">"</ins>A1<ins style="font-weight: bold; text-decoration: none;">"</ins>, lastcell) with the range you want to format, for example, Range(<ins style="font-weight: bold; text-decoration: none;">"</ins>A1:G100<ins style="font-weight: bold; text-decoration: none;">"</ins>).<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>To use the macro:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>To use the macro:</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l114">Line 114:</td>
<td colspan="2" class="diff-lineno">Line 114:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== REFERENCES ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== REFERENCES ==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>''<del style="font-weight: bold; text-decoration: none;">&quot;</del>Visual Basic User's Guide,<del style="font-weight: bold; text-decoration: none;">&quot;</del>'' version 5.0, Chapter 5<br /></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>''<ins style="font-weight: bold; text-decoration: none;">"</ins>Visual Basic User's Guide,<ins style="font-weight: bold; text-decoration: none;">"</ins>'' version 5.0, Chapter 5<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>For additional information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>For additional information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:</div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/106390&diff=49667&oldid=prev
3155ffGd: importing KB archive
2020-07-18T15:04:38Z
<p>importing KB archive</p>
<p><b>New page</b></p><div><div id="nsbanner"><br />
<br />
<div id="bannerrow1"><br />
<br />
{|<br />
| Knowledge Base<br />
|<br />
|}<br />
<br />
<br />
</div><br />
<div id="TitleRow"><br />
<br />
= <span id="KB106390"></span>XL: Unlocked Cells Not Underlined with Protection Enabled =<br />
<br />
<br />
</div><br />
<br />
</div><br />
<div id="nstext" valign="BOTTOM"><br />
<br />
Article ID: 106390<br />
<br />
Article Last Modified on 10/11/2006<br />
<br />
<br />
-----<br />
<br />
APPLIES TO<br /><br />
<br /><br />
<br />
* Microsoft Excel 5.0 Standard Edition<br />
* Microsoft Excel 5.0c<br />
* Microsoft Excel 5.0 for Macintosh<br />
* Microsoft Excel 5.0a for Macintosh<br />
* Microsoft Excel 95 Standard Edition<br />
* Microsoft Excel 95a<br />
* Microsoft Excel 97 Standard Edition<br />
* Microsoft Excel 98 for Macintosh<br />
<br />
<br />
-----<br />
<br />
<div class="notice_section"><br />
<br />
This article was previously published under Q106390<br />
<br />
</div><br />
<div class="notice_section"><br />
<br />
<br />
<br />
</div><br />
<div class="summary_section"><br />
<br />
== SUMMARY ==<br />
<br />
In Microsoft Excel versions earlier than 5.0, while worksheet protection is enabled and gridlines are turned off, unlocked cells appear underlined. This underline identifies the cells that you can edit.<br /><br />
<br /><br />
Although this feature is not available in Microsoft Excel versions 5.0 and later, this article shows how you can create a macro that allows you to simulate this behavior.<br />
<br />
</div><br />
<div class="moreinformation_section"><br />
<br />
== MORE INFORMATION ==<br />
<br />
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following Visual Basic macro places a bottom border on all unlocked cells in a worksheet. The macro also removes any bottom border from locked cells. To create the macro:<br />
<ol><br />
<li>In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, point to '''Macro''' on the '''Tools''' menu, and then click '''Visual Basic Editor'''. On the '''Insert''' menu, click '''Module'''.<br /><br />
<br /><br />
In earlier versions of Excel, point to '''Macro''' on the '''Insert''' menu, and then click '''Module'''.</li><br />
<li><p>In the module, type the following macro code:</p><br />
<pre class="codesample"> Sub Format_Unlocked_Cells()<br />
'Declare procedure-level variables<br />
Dim x As Range, lastcell As Range<br />
'Prevent screen redraw to speed up the macro<br />
Application.ScreenUpdating = False<br />
'Unprotect the worksheet to allow editing<br />
ActiveSheet.Unprotect (&quot;my_password&quot;)<br />
'Set lastcell to point to the last cell on the sheet<br />
Set lastcell = Selection.SpecialCells(xlLastCell)<br />
<br />
'Place bottom borders on unlocked cells and remove any bottom<br />
'borders from locked cells<br />
For Each x In Range(&quot;A1&quot;, lastcell)<br />
With x.Borders(xlBottom)<br />
If x.Locked = False Then<br />
.Weight = xlHairline<br />
.ColorIndex = xlAutomatic<br />
Else<br />
.LineStyle = xlNone<br />
End If<br />
End With<br />
Next x<br />
<br />
'Re-apply worksheet protection.<br />
ActiveSheet.Protect (&quot;my_password&quot;)<br />
<br />
End Sub</pre></li></ol><br />
<br />
'''NOTE''': If you want to format a specific cell range, replace Range(&quot;A1&quot;, lastcell) with the range you want to format, for example, Range(&quot;A1:G100&quot;).<br /><br />
<br /><br />
To use the macro:<br />
# Switch to the worksheet that you want to format.<br />
# In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, point to '''Macro''' on the '''Tools''' menu, and then click '''Macros'''.<br /><br />
<br /><br />
In earlier versions of Excel, click '''Macro''' on the '''Tools''' menu.<br />
# In the list of macros, click the '''Format_Unlocked_Cells''' macro. Click '''Run'''.<br />
<br />
<br />
</div><br />
<div class="references_section"><br />
<br />
== REFERENCES ==<br />
<br />
''&quot;Visual Basic User's Guide,&quot;'' version 5.0, Chapter 5<br /><br />
<br /><br />
For additional information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:<br />
<div class="indent"><br />
<br />
[[../163435|163435]] VBA: Programming Resources for Visual Basic for Applications<br />
<br />
<br />
</div><br />
<br />
</div><br />
<br /><br />
<br />
Additional query words: 5.00a 5.00c 7.00a XL98 XL97 XL7 XL5<br />
<br />
Keywords: kbdtacode kbhowto kbprogramming KB106390<br />
<br />
<div class="footer"><br />
<br />
<br /><br />
<br />
<br />
-----<br />
<br />
[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]<br />
<br />
<span>© Microsoft Corporation. All rights reserved.</span><br />
<br />
<br />
</div><br />
<br />
</div></div>
3155ffGd