before save event

[Anthony]

?Hi,

I have some code in the before save event. Its very simple, it only
hides the worksheets prior to a save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim wSheet As Variant

Worksheets("menu").Activate

'hide other ThisWorkbook.worksheets
For Each wSheet In ThisWorkbook.Worksheets

If (wSheet.Name <> "BLANK") Then wSheet.Visible = xlSheetVeryHidden

Next wSheet

End Sub

This code works perfectly every time if I run it from a save from the
toolbar or if I close the workbook down.

What it doesnt do, is where my problem lays. If I call thisworkbook.save
from VBA, the event fires and it runs through the code, but it doesnt action
anything.

The sheets dont hide, even though the code is running.


Can any one help?


Regards



Anthony

 

[Don Guillett Excel MVP]

?Hi,

    I have some code in the before save event. Its very simple, it only
hides the worksheets prior to a save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim wSheet As Variant

Worksheets("menu").Activate

'hide other ThisWorkbook.worksheets
For Each wSheet In ThisWorkbook.Worksheets

    If (wSheet.Name <> "BLANK") Then wSheet.Visible = xlSheetVeryHidden

Next wSheet

End Sub

This code works perfectly every time if I run it from a save from the
toolbar or if I close the workbook down.

What it doesnt do, is where my problem lays. If I call thisworkbook.save
from VBA, the event fires and it runs through the code, but it doesnt action
anything.

The sheets dont hide, even though the code is running.

Can any one help?

Regards

Anthony

Try putting your code in a regular module instead of a workbook event.
Then call from the workbook event or some other way such as a shape or
button....

 

[Anthony]

?"Don Guillett Excel MVP" wrote in message

?Hi,

I have some code in the before save event. Its very simple, it only
hides the worksheets prior to a save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim wSheet As Variant

Worksheets("menu").Activate

'hide other ThisWorkbook.worksheets
For Each wSheet In ThisWorkbook.Worksheets

If (wSheet.Name <> "BLANK") Then wSheet.Visible = xlSheetVeryHidden

Next wSheet

End Sub

This code works perfectly every time if I run it from a save from the
toolbar or if I close the workbook down.

What it doesnt do, is where my problem lays. If I call thisworkbook.save
from VBA, the event fires and it runs through the code, but it doesnt
action
anything.

The sheets dont hide, even though the code is running.

Can any one help?

Regards

Anthony

Try putting your code in a regular module instead of a workbook event.
Then call from the workbook event or some other way such as a shape or
button....


Hi,

Thank you for your response. I have ended up doing it the way you have
suggested and it works perfectly. (call module not the event)

Just a shame, the events there but is basically useless.

Regards

Anthony