ActiveX Button to Hide/Unhide Rows in Protected Worksheet

[Dimaa]

Hi all,

I've got a worksheet that is bilingual. I input all cells in the worksheet once, did a translation and input all that translated info into a second set of rows. The first column includes a reference, either "1" or "2", depending on whether the row uses the original or the translation. Finally, I inserted a activeX control button linked to a macro that identifies the reference in column 1, hides all the rows with a 1 and unhides all the rows with a 2 or vice versa.

I'd now like to hide the first column from the user. But when I do this, the hide/unhide function freezes. I've seen a number of solutions proposed. I prefer not to use this option since errors in the code could stop the vb while the worksheet is still unprotected:

ActiveSheet.Protect Password:="password"

[Other code here]

ActiveSheet.Unprotect Password:="password"

Internet blogs suggest that if you check the option allowing users to format rows and columns when protecting the worksheet, it should allow users to hide unhide. I think this wouldn't solve my problem since I need to prevent a column from getting unhidden but allow rows to get hidden/unhidden [which includes a bit of the protect column]. But I tried it, and even with both options checked, protecting the worksheet blocks the translation macro. This is why I've not even bothered to try programmatically using the "Allowformattingrows" property of the protection object.

I found this proposed solution:

ActiveSheet.Protect Password:="password", UserInterfaceonly:=True

I tried putting this bit of code into sub procedure [e.g., Protect()] and module of its own. I then tried calling that procedure from the translation sub procedure with something like:

Sub Translation()

Call Protect

[Other code for hiding/unhiding rows]

End Sub

That seemed to work when I run the sub procedure from the vb editor. But when I try it from the activeX control button on the worksheet, it doesn't work!

I could use people's thoughts or suggestions to solve this. Thanks.

 

[Dimaa]

Well, that's a bit silly of me. I figured out the problem while tinkering with the cell formatting. I'd only paid attention to unchecking the "locked" option for cell formatting the rows being hid/unhid since I thought the "hidden" option only referred to formulas. But I discovered that if I uncheck the "hidden" option for the rows, then using the userinterfaceonly:= True does its job.

Hope someone else finds this useful. =)