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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,899
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
 

BugBitten

New Member
Joined
Oct 8, 2009
Messages
4
Many thanks indeed mole999.

That works perfectly and is very much appreciated.
 

Tobin17

New Member
Joined
Mar 2, 2017
Messages
1
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
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top