Locked Sheets and Hiding Rows

[Newbienew]

I am having an issue with my sheets being locked and using a MACRO transition code to hide and unhide rows by clicking on a circle shape . My Transition code hides and unhides rows 16-17. I would like to still hide and unhide these rows even if the sheet is locked. But currently when i lock it, it use the macro button i get an error. Is there anything that i can do or is ther anything that can be done? Thank you for your help in advance

 

[BrianJN1]

Is the only issue one of getting an error when you try to run the macro?

If so add "ActiveSheet.Unprotect" as your first line of the macro and "ActiveSheet.Protect" as the last line.
That will ensure that the macro can do whatever adjustments are required before locking the sheet again.

 

[Peter_SSs]

As well as Brian's suggestion, there are 2 other options that may or may not suit you.

1. If you unprotect the sheet (manually) then as you go to protect it again there is an option in the list on that dialog to 'Allow all users of this worksheet to: Format rows'. If you check that box then your hide/unhide macro should work without needing to unprotect the worksheet. Of course, checking that box means that other worksheet users can also format rows (including hide/unhide) and that may not suit you.

2. If the above is not suitable, another option is to unprotect the sheet manually, then run this code (only needs to be done once) and then the sheet is protected against worksheet users but not against a macro so you can do whatever you want with a macro without needing to unprotect/reprotect.
Before running the code, change the sheet name to your sheet name and adjust the password to yours. If you are not using a password, just leave out the red section altogether.

Sub Protect_UIO()
  Sheets("sheet name").Protect Password:="abc", Userinterfaceonly:=True
End Sub

 

[vcoolio]

Further to Brian's and Peter's suggestions, if you want to set the UserInterfaceOnly to True for all worksheets (I'm assuming from your opening post that you have more than one and that you are using the same password for all sheets), you can use a Workbook_Open event placed in the Workbook module:-

Private Sub Workbook_Open()

Dim ws As Worksheet

        For Each ws In Worksheets
        ws.Protect Password:="YOUR PASSWORD HERE", UserInterFaceOnly:=True
Next ws

End Sub

Each time the workbook is opened, the code will set the UserInterfaceOnly to True thus allowing all macros to run whilst preventing any changes being made by any Users.

I hope that this helps.

Cheerio,
vcoolio.

 

[Peter_SSs]

Each time the workbook is opened, the code will set the UserInterfaceOnly to True ....

Thanks for the prod, vcoolio. My previous post may have been misleading ..

.. run this code (only needs to be done once)

.. and needs clarification. It only needs to be run once - while the workbook remains open. So, if using the user interface only idea, it would be a good idea to have Workbook_Open code whether it applies the UIO to just this sheet or all sheets.

 

[vcoolio]

No worries Peter.

BTW. Notifications are coming through OK now. It was a server problem and this blasted NBN is really proving to be a right royal pain in the you know what! My old ADSL2 connection was faster and had minimal issues!

Thanks again for looking into it Peter.

Cheerio,
vcoolio.

 

[Newbienew]

It worked perfectly, Thank you Mr. Peter and to all that replied and gave aid.

 

[Newbienew]

Sub transaction1()
activesheets.Unprotect
Rows("34:38").Hidden = Not Rows("34:38").Hidden
ActiveSheet.Protect
End Sub

I am not sure what is going on but i am getting an error now and it says that i have to debug the ROWS portion. It says it regardless of activesheet.unprotect and activesheet.protect
Is tehre any thing that can be done to correct this

 

[BrianJN1]

Sub transaction1()
activesheets.Unprotect
Rows("34:38").Hidden = Not Rows("34:38").Hidden
ActiveSheet.Protect
End Sub

I am not sure what is going on but i am getting an error now and it says that i have to debug the ROWS portion. It says it regardless of activesheet.unprotect and activesheet.protect
Is tehre any thing that can be done to correct this

When I ran your code, after I changed "activesheets" to "activesheet", no "s", it worked.

 

[Newbienew]

I still get the debug if i add that or not. Rows("34:38").Hidden = Not Rows("34:38").Hidden is highlighted each time