Hey all,
This is my first crack at VBA, so bear with me.
I'm trying to create a VBA button to hide or unhide specific rows at the user's discretion, on a protected sheet.
I've seen a lot about UserInterfaceOnly mode, but there seems to be some compatibility issues between it and the button I created.
This is what I have for the UserInterfaceOnly, applied to the This Sheet module, which is a direct copy/paste:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="relyt", _
UserInterFaceOnly:=True
Next wSheet
End Sub
My button uses this:
Sub AllergenAlert_Click()
With Worksheets("Label Claim").Rows("6:10")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
With Worksheets("Formula").Rows("9:13")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
With Worksheets("Process").Rows("8:12")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
With Worksheets("WGT").Rows("9:13")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
With Worksheets("Yield").Rows("9:13")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
With Worksheets("Pkg Record").Rows("9:13")
If .Hidden = True Then
.EntireRow.Hidden = False
Else
.EntireRow.Hidden = True
End If
End With
End Sub
I'm sure that I butchered it, and there's an easier way to write it, but it's what I could manage. This isn't something that would occur on every sheet, or on the same rows of each sheet. I'll likely be using similar code for single rows on an "Organic Alert" button, as well as adding a few sheets to it. The code functions (albeit a little slowly) when the sheets aren't protected.
I had thought that maybe the Allergen Alert code needs to be on the This Workbook module with the UserInterfaceOnly mode, but that just rendered my button unusable, and I know that the UserInterfaceOnly code needs to be in This Workbook, so I'm not sure where I'm going wrong. The error occurs right out of the gate, at the first .EntireRow.Hidden = False, and it hits me with an error that says: Run Time Error '1004': Unable to set the Hidden property of the Range class.
Thank you in advance.
Bookmarks