Delete WB-Open sub as WB is saved

[blackie42]

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]

Not tested, but try something like this

[vba]

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
[/vBa]

[Jan Karel Pieterse]

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