[RESOLVED] BeforeClose

[mikeymay]

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?

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

[si_the_geek]

Moved to Office Development forum.

[zaza]

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

[mikeymay]

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.

[RobDog888]

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.

[mikeymay]

Don't know.

Just seemed to work......

What should I use instead of auto_open?

[RobDog888]

Private Sub Workbook_Open()
    MsgBox "Open"
End Sub

[mikeymay]

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

[RobDog888]

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

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

Then call as usual from ThisWorkbook.

Private Sub WorkBook_Open()
    SomeSubOfMine
End Sub

[mikeymay]

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

[RobDog888]

Np, dont forget to Resolve the thread then.