Run-Time Error '1004': Method 'Visible of Object' failed

[jwelker12]

I am trying to Hide all of my sheets except for one sheet before I close the file and I keep getting an error and I was wondering if there is something that I need to change. The weird thing is that I only get the error when I use the file in my Mac Version of Microsoft Excel and when I use a PC Microsoft Excel it works just fine for some reason.

Here is the formula that is entered in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Declare your variables
Dim ws As Worksheet

'Step 2: Unhide the Starting Sheet
Sheets("START").Visible = xlSheetVisible

'Step 3: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 4: Check each worksheet name
If ws.Name <> "START" Then

'Step 5: Hide the sheet
ws.Visible = xlVeryHidden
End If

'Step 6: Loop to next worksheet
Next ws

'Step 7: Save the workbook
ActiveWorkbook.Save

End Sub


Private Sub Workbook_Open()

'Step 1: Declare your variables
Dim ws As Worksheet

'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 3: Unhide the Sheet
Sheets("League Set Up").Visible = xlSheetVisible

'Step 4: Loop to next worksheet
Next ws

'Step 5: Hide the Start Sheet
Sheets("START").Visible = xlVeryHidden

End Sub




If anyone is able to help me please let me know if there is something i am missing.

Thank you
Jay

 

[dmt32]

Hi, welcome to Forum

see if this alternative approach solves your problem:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Step 1: Declare your variables
    Dim ws As Integer


    'Step 2: Unhide the Starting Sheet
    With Sheets("START")
        .Visible = xlSheetVisible
        .Move Before:=Sheets(1)
    End With


    'Step 3: Start looping through all worksheets
    For ws = 2 To ThisWorkbook.Sheets.Count

        'Step 4: Hide the sheet
        Sheets(ws).Visible = xlVeryHidden
    
    'Step 5: Loop to next worksheet
    Next ws


    'Step 6: Save the workbook
    ActiveWorkbook.Save


End Sub

Dave

 

[jwelker12]

Hi, welcome to Forum

see if this alternative approach solves your problem:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Step 1: Declare your variables
    Dim ws As Integer


    'Step 2: Unhide the Starting Sheet
    With Sheets("START")
        .Visible = xlSheetVisible
        .Move Before:=Sheets(1)
    End With


    'Step 3: Start looping through all worksheets
    For ws = 2 To ThisWorkbook.Sheets.Count

        'Step 4: Hide the sheet
        Sheets(ws).Visible = xlVeryHidden
    
    'Step 5: Loop to next worksheet
    Next ws


    'Step 6: Save the workbook
    ActiveWorkbook.Save


End Sub

Dave

Hello Dave,

Thank you for the information but it was still unable to work. It actually came up with another error this time.
Then new error was Run-time error 1004 - Unable to set the visible property of the worksheet class.

Any Ideas?

Thanks again
Jay

 

[dmt32]

is the workbook or worksheets(s) protected?

Dave

 

[jwelker12]

is the workbook or worksheets(s) protected?

Dave

Hello Dave,

Yes all of the worksheets are protected.

Jay

 

[dmt32]

Have you also protected the workbook? if so, you will need to unprotect it before rest of your code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Declare your variables
    Dim ws As Integer

    With ThisWorkbook
            .Unprotect Password:=""
    
    'Step 2: Unhide the Starting Sheet
    With .Sheets("START")
        .Visible = xlSheetVisible
        .Move Before:=Sheets(1)
    End With

    'Step 3: Start looping through all worksheets
    For ws = 2 To .Sheets.Count


        'Step 4: Hide the sheet
        .Sheets(ws).Visible = xlVeryHidden
    
    'Step 5: Loop to next worksheet
    Next ws
    
        .Protect Password:=""


    'Step 6: Save the workbook
        .Save
    End With


End Sub

Dave

 

[jwelker12]

Dave,

No the workbook is not protected just the individual sheets.

Jay

 

[jwelker12]

Hello Dave,

I still got an error when i did that this time the error was: 1004 method close of object workbook failed:

Here is what it says the error is:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


'Step 1: Declare your variables
Dim ws As Integer


With ThisWorkbook
.Unprotect Password:="keyword12"

'Step 2: Unhide the Starting Sheet
With .Sheets("START")
.Visible = xlSheetVisible
.Move Before:=Sheets(1)
End With


'Step 3: Start looping through all worksheets
For ws = 2 To .Sheets.Count




'Step 4: Hide the sheet
.Sheets(ws).Visible = xlVeryHidden

'Step 5: Loop to next worksheet
Next ws

.Protect Password:="keyword12"




'Step 6: Save the workbook
.Save
End With




End Sub


Don't know why this one is giving me so much trouble.

Thank you
Jay.

 

[dmt32]

Change step 4

.Sheets(ws).Visible = xlSheetVeryHidden

Dave

 

[jwelker12]

Same Error haha. This one is a head scratcher.....