locked VBAProject problems

F

Fan924

I was putting finishing touches on my project. Everything was working
fine. Then, I locked the VBAProject. Now I have problems.

The project hides all sheets but one, does a save, then unhides the
sheets. When it unhides it's first sheet, it creates a "sheet tab"
that says Module6 then finishes unhiding sheets. If you open "Module6
tab", excel crashes.

This is spliced together code from the archives. I marked the exact
spot where the "Module6 tab" occurs (---->*****).
Any way to fix this? It's excel 97.

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim fName As Variant
Dim LastSheet As Variant
LastSheet = ActiveSheet.Name
Application.EnableEvents = False
On Error GoTo ErrHandler
Call Workbook_Hide2
Cancel = True
If SaveAsUI Then
fName = Application.GetSaveAsFilename()
If fName = "False" Then
GoTo ErrHandler:
Else
ActiveWorkbook.SaveAs fName
End If
Else
ThisWorkbook.Save
End If
ErrHandler:
Call Workbook_UnHide2
ThisWorkbook.Saved = True
Cancel = True
Application.EnableEvents = True
On Error Resume Next
Worksheets(LastSheet).Select
End Sub

Private Sub Workbook_UnHide2()
Application.EnableEvents = False
Dim s As Integer
' Unhides All Sheets, Hide First Sheet Only
Application.ScreenUpdating = True
For s = 2 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(s).Visible = True
---->*****
Next s
ActiveWorkbook.Worksheets(1).Visible = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub Workbook_Hide2()
Dim s As Integer
' Hides All Sheets, Unhide First Sheet Only
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(1).Visible = True
For s = 2 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(s).Visible = False
Next s
Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top