[RESOLVED] BeforeClose

I have the following routines inThisWorkbook but althougg I can get the auto_open to work I can't get the BeforeClose to work.

Anyone have any ideas?

VB Code:

Option Explicit
 
Private Sub auto_open()
 
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Error" Then
ws.Visible = xlSheetVisible
Else
End If
Next ws
 
Worksheets("Error").Visible = xlSheetVeryHidden
 
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Application.EnableEvents = False
 
MsgBox ("Closing")
 
If Not ThisWorkbook.Saved Then
Else
Worksheets("Error").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Error" Then
ws.Visible = xlSheetVeryHidden
Else
ws.Visible = xlSheetVisible
End If
Next ws
ThisWorkbook.Save
End If
 
End Sub

Thanks

Moved to Office Development forum.

Shall I go through your code and first decipher what it supposed to do before trying to figure out how it isn't working? Or can you answer those questions and make it a lot easier?

zaza

Actually I think I have just cracked it.

Need to restart Excel when after the code has been dropped in. Not sure why, but it seems to work.

Why would you need to restart Excel to make this work?

What version of Excel are you running? You shouldnt use the Auto_Open event as its old and only for backwards compatibility.

Don't know.

Just seemed to work......

What should I use instead of auto_open?

VB Code:

Private Sub Workbook_Open()
    MsgBox "Open"
End Sub

:)

In the Auto_Open (nowWorksheet_Open) I want to place a call to another routine that is in a module.

It doesn't seem to recognise the sub name. How do I call another routine when script is behind ThisWorkbook?


Thanks

The sub in the module should be declared as Friend or Public.

VB Code:

Public Sub SomeSubOfMine()
    MsgBox "Meow!"
End Sub

Then call as usual from ThisWorkbook.

VB Code:

Private Sub WorkBook_Open()
    SomeSubOfMine
End Sub

Right, I think I have it tied down now. Thanks all for the help.

Np, dont forget to Resolve the thread then. ;)