Protected Workbook loss of standard green cell border

BugBitten

New Member
Joined
Oct 8, 2009
Messages
4
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Rich (BB code):
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top