triggering a macro by workbook_close or workbook_open

[Byers]

I've been getting some help on this, and I have worksheets that I'd lik
to veryhide on closing, and unhide on opening if macros are enabled
otherwise it displays a warning worksheet. what I have been doing i
just hiding it all before I close it, but I'm thinking what if someon
else uses my template, opens it with macros, closes it, then open
without macros, then it won't work anymore, and they will be able t
see it. so I created the following code, but it isn't working..
please help.


Private Sub Workbook_Open()

ActiveWorkbook.Unprotect password:="******"
Sheets("template").Visible = True
Sheets("cover").Visible = True
Sheets("summary").Visible = True
Sheets("staffing schedule").Visible = True
Sheets("hours").Visible = True
Sheets("costs (client)").Visible = True
Sheets("costs (internal)").Visible = True
Sheets("ot hrs").Visible = True
Sheets("ot costs (client)").Visible = True
Sheets("ot costs (internal)").Visible = True
Sheets("expense").Visible = True
Sheets("warning").Visible = xlVeryHidden
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("template").Select
ActiveWorkbook.Protect password:="******"

End Sub

Private Sub Workbook_Close()

ActiveWorkbook.Unprotect password:="******"
Sheets("template").Visible = xlVeryHidden
Sheets("cover").Visible = xlVeryHidden
Sheets("summary").Visible = xlVeryHidden
Sheets("staffing schedule").Visible = xlVeryHidden
Sheets("hours").Visible = xlVeryHidden
Sheets("costs (client)").Visible = xlVeryHidden
Sheets("costs (internal)").Visible = xlVeryHidden
Sheets("ot hrs").Visible = xlVeryHidden
Sheets("ot costs (client)").Visible = xlVeryHidden
Sheets("ot costs (internal)").Visible = xlVeryHidden
Sheets("expense").Visible = xlVeryHidden
Sheets("warning").Visible = True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("warning").Select
ActiveWorkbook.Protect password:="******"

End Sub

I'm sure it's just a little thing that I'm missing, but I can't figur
it out.

thanks

 

[Frank Kabel]

Hi
you have to save the workbook in the Beforeclose statement

 

[JE McGimpsey]

You can't just make up your own events - there's no Worbook_Close event.

However, you can use the Workbook_BeforeClose() event.

make sure both macros are in the ThisWorkbook code module.

 

[Byers]

I've changed the action to workbook_beforeclose()
and put them both in the thisworkbook module and I'm getting som
errors... probably a quick fix for those smarter than I.

When I close the workbook I get
"Compile Error:

Procedure Declaration does not match description of event or procedur
having the same name"
and "Private sub workbook_beforeclose()" is highlighted in the VB
editor.

when opening it back up I recieve:
"compile error in hidden module: thisworkbook"

my thisworkbook module read:

Private Sub Workbook_Open()

ActiveWorkbook.Unprotect password:="******"
Sheets("template").Visible = True
Sheets("cover").Visible = True
Sheets("summary").Visible = True
Sheets("staffing schedule").Visible = True
Sheets("hours").Visible = True
Sheets("costs (client)").Visible = True
Sheets("costs (internal)").Visible = True
Sheets("ot hrs").Visible = True
Sheets("ot costs (client)").Visible = True
Sheets("ot costs (internal)").Visible = True
Sheets("expense").Visible = True
Sheets("warning").Visible = xlVeryHidden
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("template").Select
ActiveWorkbook.Protect password:="******"

End Sub

Private Sub Workbook_BeforeClose()

ActiveWorkbook.Unprotect password:="******"
Sheets("template").Visible = xlVeryHidden
Sheets("cover").Visible = xlVeryHidden
Sheets("summary").Visible = xlVeryHidden
Sheets("staffing schedule").Visible = xlVeryHidden
Sheets("hours").Visible = xlVeryHidden
Sheets("costs (client)").Visible = xlVeryHidden
Sheets("costs (internal)").Visible = xlVeryHidden
Sheets("ot hrs").Visible = xlVeryHidden
Sheets("ot costs (client)").Visible = xlVeryHidden
Sheets("ot costs (internal)").Visible = xlVeryHidden
Sheets("expense").Visible = xlVeryHidden
Sheets("warning").Visible = True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("template").Select
ActiveWorkbook.Protect password:="******"

End Su

 

[Frank Kabel]

Hi
the procedure name is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code
End Sub

 

[Byers]

thank