Hiding locked rows on protected sheet other than active sheet

0
votes

I'm trying to figure out how to hide locked rows using VBA code. Let's say I have 3 password protected worksheets, with all cells locked and UserInterfaceOnly:=True.

My ActiveX button is on Sheet1. I want to hide Rows("1:2") in all of the Worksheets (Sheet1, Sheet2 and Sheet3). However, the code works fine just for the Sheet the button is on, Sheet1. When it comes to hiding rows on Sheet2/Sheet3, Excel says that I can't hide that range because it is locked. However, it's locked on Sheet1 too, but it simply works and hides/unhides rows 1:2.

I've already made a solution, unprotecting those 2 other sheets right before hiding/unhiding the rows and then protecting them again. This, however, doesn't look OK to me and makes the Excel go between all those sheets which takes some (very little) time and "flashes" when doing so. This is unwanted for me. The code looks like this:

Rows("1:2").EntireRow.Hidden = True

Sheet2.Unprotect Password:=pwd
Sheet2.Rows("1:2").EntireRow.Hidden = True
Sheet2.Protect Password:=pwd, AllowFormattingCells:=True, UserInterfaceOnly:=True

Sheet3.Unprotect Password:=pwd
Sheet3.Rows("1:2").EntireRow.Hidden = True
Sheet3.Protect Password:=pwd, AllowFormattingCells:=True, UserInterfaceOnly:=True

As you can see, I'm not unprotecting Sheet1 and it works.

Is there any way to achieve this or would it always work only on the worksheet which the ActiveX button is on? Also, I don't want to use AllowFormatingRows:=True.

Thanks

2
vbaexcelmacros
You could try this, I'm not sure if it works though. Add a worksheet variable (Dim WS as WorkSheet) and then loop through all the worksheets (For Each WS in ThisWorkBook) and the put an if statement inside of the loop (If Not WS = ActiveWorkSheet Then) and you put your code in there. If the active worksheet is always the same you could make the if statement like this: If WS.name <> "active worksheet name goes here" Then. Try this and give us a short update if it works. – RobK

2 Answers

2
votes

I'm affraid, that you should use Unprotect/Protect commands. It will not work without that.

But, you can try to use Application.ScreenUpdating flag to remove the "flashes".

Application.ScreenUpdating = False 

Rows("1:2").EntireRow.Hidden = True

Sheet2.Unprotect Password:=pwd
Sheet2.Rows("1:2").EntireRow.Hidden = True
Sheet2.Protect Password:=pwd, AllowFormattingCells:=True, UserInterfaceOnly:=True

Sheet3.Unprotect Password:=pwd
Sheet3.Rows("1:2").EntireRow.Hidden = True
Sheet3.Protect Password:=pwd, AllowFormattingCells:=True, UserInterfaceOnly:=True

Application.ScreenUpdating = True
2
votes

To prevent the screen flashing, you can set Application.ScreenUpdating = False before running your macro, and return it to True afterwards.

As for being able to hide rows without unprotecting the sheet they are on first, I don't think that is possible. Frankly I am surprised it worked on the ActiveSheet while that was protected in the first place.

Finally, and this is just me nitpicking, I would qualify what sheet that first line of code works on as well - being explicit with what objects you work on is a good way to prevent unexpected behavior.