fixing error run time error 1004 to hide specific sheets

[abdelfattah]

hello
i have this code trying hide specific sheets but it gives me error run time error 1004 "method visible of object_ worksheet failed " in this line

sh.Visible = xlSheetVeryHidden

Private Sub CommandButton2_Click()
Application.Visible = True
Sheets("micro").Activate
Dim sh As Worksheet

For Each sh In Worksheets(Array("RAW", "Date", "MICC", "REPORT", "LABLE"))
    sh.Visible = xlSheetVeryHidden
Next sh
UserForm1.hide

End Sub

i truly appreciate if anybody help

 

[dmt32]

Hi,
Is the workbook protected?

Dave

 

[abdelfattah]

hi, dmt32 absolutely not

 

[dmt32]

in that case, try following update to your code & see if helps

Private Sub CommandButton2_Click()
    Application.Visible = True
    Dim sh As Worksheet
   
    With ThisWorkbook.Worksheets("micro")
        .Visible = xlSheetVisible
        .Activate
    End With
   
    For Each sh In ThisWorkbook.Worksheets(Array("RAW", "Date", "MICC", "REPORT", "LABLE"))
        sh.Visible = xlSheetVeryHidden
    Next sh
    UserForm1.Hide

End Sub

Dave

 

[abdelfattah]

thanks so much for your updating but i would understand me why my code doesn't work i would make clear above code works for commandbutton1 but the commandbutton2 doesn't work despite the same code the difference is name of sheet this not work

Private Sub CommandButton2_Click()
Application.Visible = True
Sheets("raw").Activate
Dim sh As Worksheet
For Each sh In Worksheets(Array("MICRO", "Date", "MICC", "REPORT", "LABLE"))
    sh.Visible = xlSheetVeryHidden
Next sh
UserForm1.hide
End Sub

i hope you can answer me

 

[dmt32]

Your code for CommandButton2 does not include the updates I made

Private Sub CommandButton2_Click()
    Application.Visible = True
    Dim sh As Worksheet
  
    With ThisWorkbook.Worksheets("raw")
        .Visible = xlSheetVisible
        .Activate
    End With
  
    For Each sh In ThisWorkbook.Worksheets(Array("RAW", "Date", "MICC", "REPORT", "LABLE"))
        sh.Visible = xlSheetVeryHidden
    Next sh
    UserForm1.Hide

End Sub

I can only conclude that the error is possibly due to code trying to hide ALL sheets which is not possible. My update ensures that sheet is visible before the hide code runs

Dave

 

[Fluff]

@dmt32
I think you need to change the 1st sheet name in the array.

 

[dmt32]

@dmt32
I think you need to change the 1st sheet name in the array.

Thanks for catch - too busy watching F1 race - hopefully OP can correct.

Dave

 

[abdelfattah]

thanks dmt32 for all of thing your updating works well

 

[dmt32]

thanks dmt32 for all of thing your updating works well

You are welcome & thanks to @Fluff for catching my overlooking updating the array.

Dave