Hello,
Currently trying to build with Excel a small application that gather information regarding Firewall rules, I'm mainly using validation lists in cells and updating them in Activate worksheet events.
The worksheets, workbook, and code are password protected, and I have to jungle with Protect-Unprotect methods to be able to update the worksheets.
The problem I'm facing is that when I run this procedure from a change_event procedure of the "Home"(Start) worksheet, an error is raised on the line : ".Worksheets(ItemsWshtName).Visible = xlSheetVisible".
This is : Run-Time Error 1004 : Unable to set the Visible property of the worksheet class.
The weird thing is that it works with ItemsGroupsWshtName worksheet but not with ItemsWshtName worksheet, and I can't face what's wrong...
Many Thanks for your help if can solve this !
Public Sub UpdateWorksheets()
'Update the validation list and content of the worksheets through the call of the Activate event
Dim PolicyVisibilityTemp As Integer
Dim ItemsVisibilityTemp As Integer
Dim ItemsGroupsVisibilityTemp As Integer
With ThisWorkbook
ThisWorkbook.Unprotect WbkPassword
'Memorize old value of visibility
PolicyVisibilityTemp = .Worksheets(PolicyWshtName).Visible
ItemsVisibilityTemp = .Worksheets(ItemsWshtName).Visible
ItemsGroupsVisibilityTemp = .Worksheets(ItemsGroupsWshtName).Visible
'Display the worksheets and 'Activate them to update their content
.Worksheets(ItemsGroupsWshtName).Visible = xlSheetVisible
.Worksheets(ItemsGroupsWshtName).Activate
.Worksheets(ItemsWshtName).Visible = xlSheetVisible
.Worksheets(ItemsWshtName).Activate
.Worksheets(PolicyWshtName).Visible = xlSheetVisible
.Worksheets(PolicyWshtName).Activate
'Return the old value of visibility
'It is important to "hide" the worksheets in the same order as they have been activated
' because the worksheet "behind" the last activated one will become the active one after the last activated one becomes "hidden" (if the VisibilityTemp variable value was "Hidden"
.Worksheets(ItemsGroupsWshtName).Visible = ItemsGroupsVisibilityTemp
.Worksheets(ItemsWshtName).Visible = ItemsVisibilityTemp
.Worksheets(PolicyWshtName).Visible = PolicyVisibilityTemp
ThisWorkbook.Protect WbkPassword
End With
End Sub
Currently trying to build with Excel a small application that gather information regarding Firewall rules, I'm mainly using validation lists in cells and updating them in Activate worksheet events.
The worksheets, workbook, and code are password protected, and I have to jungle with Protect-Unprotect methods to be able to update the worksheets.
The problem I'm facing is that when I run this procedure from a change_event procedure of the "Home"(Start) worksheet, an error is raised on the line : ".Worksheets(ItemsWshtName).Visible = xlSheetVisible".
This is : Run-Time Error 1004 : Unable to set the Visible property of the worksheet class.
The weird thing is that it works with ItemsGroupsWshtName worksheet but not with ItemsWshtName worksheet, and I can't face what's wrong...
Many Thanks for your help if can solve this !
Public Sub UpdateWorksheets()
'Update the validation list and content of the worksheets through the call of the Activate event
Dim PolicyVisibilityTemp As Integer
Dim ItemsVisibilityTemp As Integer
Dim ItemsGroupsVisibilityTemp As Integer
With ThisWorkbook
ThisWorkbook.Unprotect WbkPassword
'Memorize old value of visibility
PolicyVisibilityTemp = .Worksheets(PolicyWshtName).Visible
ItemsVisibilityTemp = .Worksheets(ItemsWshtName).Visible
ItemsGroupsVisibilityTemp = .Worksheets(ItemsGroupsWshtName).Visible
'Display the worksheets and 'Activate them to update their content
.Worksheets(ItemsGroupsWshtName).Visible = xlSheetVisible
.Worksheets(ItemsGroupsWshtName).Activate
.Worksheets(ItemsWshtName).Visible = xlSheetVisible
.Worksheets(ItemsWshtName).Activate
.Worksheets(PolicyWshtName).Visible = xlSheetVisible
.Worksheets(PolicyWshtName).Activate
'Return the old value of visibility
'It is important to "hide" the worksheets in the same order as they have been activated
' because the worksheet "behind" the last activated one will become the active one after the last activated one becomes "hidden" (if the VisibilityTemp variable value was "Hidden"
.Worksheets(ItemsGroupsWshtName).Visible = ItemsGroupsVisibilityTemp
.Worksheets(ItemsWshtName).Visible = ItemsVisibilityTemp
.Worksheets(PolicyWshtName).Visible = PolicyVisibilityTemp
ThisWorkbook.Protect WbkPassword
End With
End Sub