hello all, I have a Macro to hide all but the first sheet in the workbook during the close event. I am using it similar to the EnableMacros Example http://www.ozgrid.com/FreeDownloads/EnableMacros.zip
The only problem is it works properly when I manually run it using the play option, but does not work properly when run by being called. It has greatly baffled me. It says something along the lines of: "Visibility method of object Worksheet failed." (The ShowAll function works just fine - both ways.)
The following code is the function as well as anything else which may be significant. (I put the message boxes in there to see what was going on.) Please note: I changed the password field for the purpose of this post but I know that the one I actually supplied is correct.
Sub HideAll()
Dim TestSheet As Worksheet
ActiveWorkbook.Unprotect Password:=""
For Each TestSheet In ThisWorkbook.Worksheets
TestSheet.Activate
MsgBox "BEFORE: " & Chr(10) & " " & TestSheet.Name & Chr(10) & " Visible = " & TestSheet.Visible
If TestSheet.CodeName = "Sheet1" Then
TestSheet.Visible = xlSheetVisible
Else
TestSheet.Visible = xlSheetVeryHidden
End If
MsgBox "AFTER: " & Chr(10) & " " & TestSheet.Name & Chr(10) & " Visible = " & TestSheet.Visible
Next TestSheet
Sheet1.Select
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
QuitProgram
End Sub
Sub QuitProgram()
'On Error Resume Next
TimeSpent = Now - Range("Login_Date_Time")
Range("Total_Time") = Range("Total_Time") + TimeSpent
MsgBox "You have spent " & format(TimeSpent, "h:mm:ss") & " this session.", vbInformation, "Time Spent"
Application.ScreenUpdating = False
[B]HideAll[/B]
Application.CommandBars("BudgetMenu").Delete
RestoreSettings
Application.CommandBars("Cell").Reset
Application.ScreenUpdating = True
ActiveWindow.Close SaveChanges:=True
Application.Quit
End Sub
Private Sub Workbook_Open()
Set DataSheet1 = Worksheets("HiddenData")
'On Error Resume Next
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:=""
[B]ShowAll[/B]
CreateBudgetMenu
If Application.WorksheetFunction.CountA(DataSheet1.Columns("B:B")) > 1 Then
Application.CommandBars("BudgetMenu").Controls("&File").Controls("&Create Budget").Enabled = False
Else
Application.CommandBars("BudgetMenu").Controls("&File").Controls("&Generate New Month").Enabled = False
Application.CommandBars("BudgetMenu").Controls("&View").Enabled = False
Application.CommandBars("BudgetMenu").Controls("&Adjust").Enabled = False
End If
DisableSettings
CustomizeRightClick
Range("Login_Date_Time") = Now
EnableSelection
ActiveWorkbook.Protect Password:="", Structure:=True
Application.ScreenUpdating = True
If Application.WorksheetFunction.CountA(DataSheet1.Columns("B:B")) > 1 Then
ReadjustNonMonthly
End If
End Sub
Sub ShowAll()
Dim TestSheet As Worksheet
For Each TestSheet In ThisWorkbook.Worksheets
TestSheet.Activate
If TestSheet.CodeName <> "Sheet1" Then
TestSheet.Visible = xlSheetVisible
End If
Next TestSheet
Sheet1.Visible = xlSheetVeryHidden
Sheet3.Visible = xlSheetVeryHidden
Sheet4.Visible = xlSheetVeryHidden
End Sub
Display More
Any help on this matter would be greatly appreciated!