How to run macro using workbook_open on multiple sheet

[Bebek]

Hello all. I'm new to the world of macros so I don't understand anything and I need help, Thank You.

I want to run my macro automatically when excel is opened, so I used Workbook_Open to solve it. But the problem is my macros just work for 1 out of 5 sheets. What I want is my macros will work 3 out of 5 sheets. Any help is greatly appreciated Thank You

Here are my macros


Sub Workbook_Open()
Dim sh As Worksheet

Set sh = Array("Sheet1", "Sheet2","Sheet3").Select

sh.Protect "123456", UserInterfaceOnly:=True
sh.EnableOutlining = True

End Sub

 

[Gokhan Aycan]

Would this work for you?

Sub Workbook_Open()
   
    Dim sh As Worksheet
    Dim arr As Variant
    Dim elem As Variant
   
    arr = Array("Sheet1", "Sheet2", "Sheet3")
   
    For Each elem In arr

        Set sh = ThisWorkbook.Worksheets(elem)
     
        sh.Protect "123456", UserInterfaceOnly:=True
        sh.EnableOutlining = True

    Next elem

    set sh = Nothing

End Sub

 

[Bebek]

Thank You very much Mr. Gokhan for your support, it was really great answer. But when I run it out, error massage box "Run-time error 1004" appears.

 

[Gokhan Aycan]

Which line is highlighted if you click debug?
Are the sheet names correct?
I didn't test the code inside for each...next loop (2 lines from your code) by the way.

I am not in front of a PC at this moment.

Edit: I wonder if procedure should start as "Private Sub" instead.

 

[Bebek]

OH, I'm really sorry Mr. Gokhan, the cause of the problem is, that I haven't unprotected the sheet before run the macro that you gave.

Thank you very much Mr. Gokhan for your support, I'm very greatful for your help. Much appreciated! Hope you have a great day Mr. Gokhan.

Warm Regards, Bebek from Indonesia