I have disabled macros but they still work??

[bondie]

Hi there!

I have a macro that hides sheets and does not save changes. When macro
are enabled, all hidden sheets will be displayed but user will not b
able to save any changes made to it. When I clicked Disable Macros, th
hidden sheets are still displayed. Why is that so? How can I make th
sheets hidden when macros are disabled?


Private Sub Workbook_BeforeClose(Cancel As Boolean)
HideData
ThisWorkBook.Saved = True
End Sub

Private Sub Workbook_Open()
UnhideData
End Sub

Sub HideData()
Dim i As Byte

Sheets("Secure").Visible = True

For i = 1 To ThisWorkbook.Sheets.Count
If Sheets(i).Name <> "Secure" Then
Sheets(i).Visible = xlVeryHidden
End If
Next i

End Sub

Sub UnhideData()
Dim i As Byte

For i = 1 To ThisWorkbook.Sheets.Count
If Sheets(i).Name <> "Secure" Then
Sheets(i).Visible = True
End If
Next i
Sheets("Secure").Visible = xlVeryHidden

End Sub

Thanks in advance

 

[John Wilson]

bondie,

What you're doing is hiding the sheets on the Before_Close Event.
You need to hide the sheets on the Before_Save Event.

Look at it this way....
You manually hide some sheets.
You open with macros enabled and your code unhides them.
Then you save the sheet.
It's saved with those sheets visible.
When you close, you hide the sheets but the "saved" copy
still has them visible, therefore when you reopen with macros
disabled, they'll be visible.

There is a caveat to using the Before_Save event to do this.
Mainly, when the user saves, it hides the sheets and then the
workbook is unusable until they close and repoen (or run your
"UnhideData" macro).

John

 

[Alan]

There is a caveat to using the Before_Save event to do this.
Mainly, when the user saves, it hides the sheets and then the
workbook is unusable until they close and repoen (or run your
"UnhideData" macro).

Is there *any* event *after* a save that was initiated by closing the
workbook?

As far as I can tell, the save event is triggered first in that
scenario, and then the save event is triggered.

Is there anything that can then be 'caught' after the save happens,
but before the workbook is gone?

Thanks,

Alan.

 

[NICK]

Before you exit the workbook, hide the relevant sheets,
protect the workbook and then save it, should do the job

NICK

 

[NICK]

Before you exit the workbook, hide the relevant sheets,
protect the workbook and then save it, should do the job

NICK

 

[John Wilson]

Alan,

One way that I know of around that caveat.
In the "Secure" worksheet code add the following:

Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "UnhideData"
End Sub

Given the code that the OP supplied and using the
the Before_Save Event as opposed to the Before_Close Event
what the above does is.......
When the user saves the workbook, the "HideData" sub is called.
Within that sub, the "Secure" worksheet will be activated.
The code above sets a one second delay before executing
the "UnhideData" sub. That pause allows the workbook to be
saved with all the sheets hidden except for "Secure" and then
fires the "UnhideData" sub to unhide the sheets.

P.S. I noticed that the OP (is that you) used
ThisWorkbook.Saved = True
That doesn't save the workbook. It only tells Excel that it should
consider it as already saved whether it was or not.

John

 

[bondie]

Hi John!

I have placed the HideData code in workbook_beforesave event and it now
works Thanks for your help!!

 

[John Wilson]

Alan,

Is there *any* event *after* a save that was initiated by closing the
workbook?

There isn't any "After_Save Event

As far as I can tell, the save event is triggered first in that
scenario, and then the save event is triggered.

In the scenario the OP provided there wasn't a save.
ThisWorkBook.Saved = True
doesn't save the workbook

Is there anything that can then be 'caught' after the save happens,
but before the workbook is gone?

After a save, the workbook isn't gone. It's still opened.
In the course of the save, the OP was activating a previously hidden sheet.
I used the activate event on that sheet and a timer to simulate
an After_Save Event.

John

 

[gocush]

blondie

Just out of curiosity, I noticed that you are dimensioning the
variable i as Byte.

Then you use i to count the sheets in your workbook. The sheet count
will always be an Integer.

Does this ever cause a Type Mismatch error?

 

[bondie]

hi gocush.. so far it has been working fine for me