cell border formating

  • Thread starter Thread starter Carl Johnson
  • Start date Start date
C

Carl Johnson

I have a protected worksheet with certain cells made available to the user
for data entry that they can tab through. However if the user would click
outside the available cells with their mouse the cell is highlighted by a
thick border. Is there a way that I can eliminate this? Thank you.
 
Hi Carl,

This is available in XP - don't know about other versions.
If all other cells are locked, when you set the sheet
protection you have an option list that contains an item
to allow users to select locked cells. If this is
available to you just remove the tick from that option.
Also, this is the first item in the options list.

Biff
 
If you don't have Excel 2002 (XP) you can still accomplish this using a
macro. The following routine was placed in the ThisWorkbook object of a test
workbook. It check each sheet to see if it's protected. If so, it prevents
selection of locked cells. This macro must run each time the workbook opens,
which is why it needs to be placed in a special place and called by its
special name.

Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents Then wks.EnableSelection = xlUnlockedCells
Next wks

End Sub

To place it in your workbook:

1. Copy it
2. In Excel, press ALT+F11 to access the Visual Basic Editor (VBE)
3. Locate the name of the desired workbook in the Project Explorer
(usually on the right side of the VBE window. If the explorer is not there,
press Ctrl+R to bring it up.
4. Double-Click the workbook name
5. Locate and double-click the "ThisWorkbook" object under the workbook
name
6. Paste the above code in the white window that appears.
7. Save and close, then re-open the workbook to test.
 
Thanks Robert, works great.
Robert Rosenberg said:
If you don't have Excel 2002 (XP) you can still accomplish this using a
macro. The following routine was placed in the ThisWorkbook object of a test
workbook. It check each sheet to see if it's protected. If so, it prevents
selection of locked cells. This macro must run each time the workbook opens,
which is why it needs to be placed in a special place and called by its
special name.

Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents Then wks.EnableSelection = xlUnlockedCells
Next wks

End Sub

To place it in your workbook:

1. Copy it
2. In Excel, press ALT+F11 to access the Visual Basic Editor (VBE)
3. Locate the name of the desired workbook in the Project Explorer
(usually on the right side of the VBE window. If the explorer is not there,
press Ctrl+R to bring it up.
4. Double-Click the workbook name
5. Locate and double-click the "ThisWorkbook" object under the workbook
name
6. Paste the above code in the white window that appears.
7. Save and close, then re-open the workbook to test.

--
________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
Click to expand...
 
Back
Top