Protected Workbook loss of standard green cell border

[BugBitten]

I am seeking to run various sheets within a protected worksheet / workbook structure. I don't want to have code running within various routine's that continually have to unprotect/protect various worksheets, as this is slowing the use of the workbook down considerably.

Opening the workbook, my code looks like this:

Private Sub Workbook_Open()

Dim wSheet As Worksheet

For Each wSheet In Worksheets

wSheet.Protect Password:="password", UserInterFaceOnly:=True
wSheet.EnableSelection = xlUnlockedCells


Next wSheet
​

Sheet1.Range("B18").Select


End Sub

As my actual project is large, I simply set about setting up a test workbook with a small range of unlocked cells in sheet1 (range A16:D20), of which B18 is one. I also have a blank sheet2.

This works very well with the exception that there is no 'green border' around the (example only) selected cell B18. If I click anywhere in the unlocked range above after first load, the green cell border doesn't appear.

If I click to sheet 2 and then back to sheet 1, the green border appears, but this isn't the perfect answer to the problem.

This only seems to occur when I run the above protection routine at start up of the workbook. It doesn't occur when protection is run within each workbook separately, although this slows down the workbook considerably so I don't want to do this.

I'm using Office 365 / latest excel.

Computer operating Windows 7 Professional.

I hope I've posted this properly and I would be very grateful for any help you can offer to resolve this matter.

 

[mole999]

Private Sub Workbook_Open()
application.screenupdating = false
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="password", UserInterFaceOnly:=True
wSheet.EnableSelection = xlUnlockedCells
Next wSheet
application.screenupdating = true
Sheet1.Range("B18").Select
End Sub

 

[BugBitten]

Many thanks indeed mole999.

That works perfectly and is very much appreciated.

 

[Tobin17]

I have a similar problem. I have a workbook with multiple tabs, some of which are hidden. On each worksheet, most of the cells are locked. The users can only enter data in a few of the cells to protect formulas that I have in other cells. Sometimes when I open the workbook the green outline around the active cell is missing. If I click onto another worksheet the outline returns. But, I want to always have the active cell outlined in green. Here is the code in my workbook. I tried the suggestion from the thread but it did not work for me. Any suggestions?


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wsh As Worksheet
For Each wsh In Worksheets
wsh.EnableOutlining = True
wsh.Protect UserInterfaceOnly:=True
wsh.EnableSelection = xlUnlockedCells
Next wsh
Application.ScreenUpdating = True
End Sub

 

[amthomas]

I know this is old and it is 2024 when I am replying, but just in case someone else runs into this like I did (which was still helpful):

I had manually protected sheets (using Excel controls). I had to call the wsh.Unprotect before applying the Protect with UserInterfaceOnly (as this seems to be a VBA only mechanism). I have not robustly tested this but my workbook is working as I expect now and does not have the vanishing selection indicator. I would suggest to anyone running across this to try that out

Such an odd and old problem apparently. I am surprised there are so few references on searches for it. Best of luck!