PDA

View Full Version : Delete WB-Open sub as WB is saved



blackie42
10-22-2009, 06:55 AM
I have a Workbook_open event that hides all but one of the sheets.

I then have a sub that allows the user to select the sheets they want to keep visible and use.

I want to disable the WB-Open routine as the WB is saved for the first time (so next time its opened the sheets selected will appear only). I will prompt a rename of the WB at that point

Assume the instruction would be in the 'before save' or 'before close' event.

Not sure how to go about deleting the WB_open code.

Any help appreciated
regards
Jon

Bob Phillips
10-22-2009, 07:11 AM
Not tested, but try something like this



Private Sub Workbook_Open()
Dim fReOpen As Boolean

On Error Resume Next
fReOpen = Application.Evaluate(Me.Names("_ReOpen").RefersTo)
On Error GoTo 0
If Not fReOpen Then

Call HideAllSheets
ThisWorkbook.Names.Add Name:="_ReOpen", RefersTo:="=TRUE"
End If

End Sub

Jan Karel Pieterse
10-22-2009, 07:17 AM
Alternatively, make your workbook a template.
A freshly opened file from a template has no path:

If Thisworkbook.Path="" and not Thisworkbook.Name Like "*.xlt" Then
'Hide sheets here
End If