Excel Problem with hiding/diplaying protected wroksheets

[Dougy]

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&quot
.Worksheets(ItemsGroupsWshtName).Visible = ItemsGroupsVisibilityTemp
.Worksheets(ItemsWshtName).Visible = ItemsVisibilityTemp
.Worksheets(PolicyWshtName).Visible = PolicyVisibilityTemp
ThisWorkbook.Protect WbkPassword
End With
End Sub

 

[xlbo]

where are you declaring what ItemsWshtName and ItemsGroupsWshtName are ???
Being as this is what is causing the error - it might be handy....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Dougy]

Thanks for taking care of this case, xlbo.

They are delcared as constant in the public Module "Module1" :

'Worksheet names
Public Const MainWshtName As String = "Home"
Public Const UsersWshtName As String = "Users"
Public Const ServicesWshtName As String = "Services"
Public Const ServicesSelectionWshtName As String = "Service(s) Selection"
Public Const ItemsWshtName As String = "Items"
Public Const ItemsGroupsWshtName As String = "Items Groups"
Public Const PolicyWshtName As String = "Policy"

Those values are infact the worksheet name that I sent for each of the Excel Worksheet (double-click on the tab, and manually entered)

 

[Bowers74]

I'm guessing here, but wouldn't it be easier to name the sheets in the VBE (i.e. Select the sheet in the TreeView, select properties and give them a name like sHome). That way you wouldn't have to have all of the Public Const declarations.

Or maybe I missed something.

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Check the name of this "Items" worksheet - a 1004 error is almost always because you are trying to reference something that isn't there - make sure there is no space at the start or end of the name in the worksheet. Also, try putting a watch on the variable - make sure it doesn't get set to nothing at some point.

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Dougy]

I tried a debug with a break on value change for ItemsWshtName but nothing changed.

This constant is used everywhere in the code and this the
only part where ther is a problem.

It is weird, because it is perfectly working when I disable all the worksheet and workbook protection features in my code.

This make me think of a bug of Microsoft Excel VBA about worksheet protection...

 

[xlbo]

Shouldn't be a problem here - especially as you are using the same method for the ItemsGroupWshtName

Have you tried hardcoding the name in so:
.Worksheets(ItemsWshtName).Visible = xlSheetVisible
.Worksheets(ItemsWshtName).Activate

becomes
.Worksheets("Items&quot.Visible = xlSheetVisible
.Worksheets("Items&quot.Activate



Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Dougy]

Hi Geoff,

Sorry for being so long, I was on vacation for 2 weeks.

To answer you question. Yes, I did tried to hardcopy the name, but the error appeared again however...

In all cases, if I step over the two lines (by puting them into comments) :
.Worksheets("Items&quot.Visible = xlSheetVisible
.Worksheets("Items&quot.Activate
then the line :
.Worksheets(PolicyWshtName).Visible = xlSheetVisible
also cause the same problem.

When I'm in debug mode, I can really see that ThisWorkbook.Worksheets("Items&quot.Visible has a value of 2

The fact is that Excel can really not modify the value of this property. It refuses to modify it.
There must have somehow a bug that prevent to modify the visible property of a worksheet when some of the pasword protection feature is activated (worksheet, workbook or VBA).

I say it again. This procedure is working perfectly well when no password protection is used.

Many Thanks for your care

 

[Bowers74]

Dougy,

have you even considered my suggestion? Or would it be too much trouble to go through your code and replace Worksheets("Items&quot.Visible = xlSheetVisible with sItems.Visible = True[/b]

This way, you won't have to worry about all of the Constant declarations, and clearing up your memory after you're done.

It's just a suggestion, but I thought that maybe it got lost somewhere in the rest.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[combo]

Hi Doughy,
by definition, when you protect workbook structure you can't add, delete hide and unhide sheets. Both manually and by code.
Consider rather temporarily unprotect workbook to change sheet's visibility.

combo

 

[Bowers74]

combo,

That is what the first line of his code is doing.

ThisWorkbook.Unprotect WbkPassword

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Dougy]

Hi Mike,

Thanks !
Ok, I just tried your suggestion. Unfortunately it doesn't solved my problem. Still got the same error message.

Should I really need to take care of clearing up memory when I'm using concstant ? I thought Excel was managing it (although badly...).


Really weird. I even unprotected the related worksheets, but the problem remains the same.

Any other idea to solve my problem ?
Thanks

Dougy