Specifying multiple sheets in OpenWorkbook

[TheJay]

Hello there, I have code that I want to run on two specific (named) sheets. I'm not sure what I need to do.

Private Sub Workbook_Open()
    Dim sh          As Worksheet
    For Each sh In Worksheets
[B]        If sh.Name = "Property Numbering", "VO Areas" Then[/B]
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
                Range("C13").Select
                Selection.ClearContents
                Range("C8").Select
                Selection.ClearContents
                Range("B2").Select
                Selection.ClearContents
                Range("B2").Select
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub

It doesn't like this:

[B]If sh.Name = "Property Numbering", "VO Areas" Then[/B]

How do I correct this please?

Thanks.

 

[Jaafar Tribak]

Private Sub Workbook_Open()
    Dim sh         As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Or sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C13,C8,B2").ClearContents
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub

BTW, you don't need to select cells in order to clear their contents.

 

[TheJay]

Thank you, that's very informative. How about keeping the formatting rules to these two sheets but clearing different cells in different sheets?
"Property Numbering"
sh.Range("C13,C8,B2").ClearContents

"VO Areas"
sh.Range("C4").ClearContents

 

[Jaafar Tribak]

Thank you, that's very informative. How about keeping the formatting rules to these two sheets but clearing different cells in different sheets?
"Property Numbering"
sh.Range("C13,C8,B2").ClearContents

"VO Areas"
sh.Range("C4").ClearContents

Private Sub Workbook_Open()
    Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C13,C8,B2").ClearContents
        ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub

 

[TheJay]

Okay, encountered unexpected behavior with this code.

It doesn't like a range to be specified when one of the cells includes a merge, so had to go back to specifying individual.

Then, I found that all the code was being executed on the second specified sheet and nothing on the first. So, it cleared data from a list on the VO Areas based on

   Range("C13").Select
            Selection.ClearContents
            Range("C8").Select
            Selection.ClearContents
            Range("B2").Select
            Selection.ClearContents

Then added "Property Reference Guide (Click Arrow to Start)" to cell B2 based on

            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Range("B2").Select

It then executed the expected code:

    ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            Range("C4").Value = "'Choose P"
            Range("C4").Select

Overall code:

Private Sub Workbook_Open()
    Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
'sh.Range("C13,C8,B2").ClearContents
            Range("C13").Select
            Selection.ClearContents
            Range("C8").Select
            Selection.ClearContents
            Range("B2").Select
            Selection.ClearContents
            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Range("B2").Select
        ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            Range("C4").Value = "'Choose P"
            Range("C4").Select
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub

 

[TheJay]

I'm not sure how to fix this.

 

[TheJay]

Does anyone have any ideas how to only execute the code on the specified page and prevent it all from running on the visible sheet instead?

 

[TheJay]

I've unmarked this as resolved because of the bug, could someone please get back to me? Thanks.

 

[Jaafar Tribak]

I am confused as to what the issue is . If you can rephrase what you want to happen while giving specific details ie:- sheets, cells etc ...

 

[TheJay]

Thanks for your reply.

The workbook has multiple worksheets. The first is called Property Numbering and the second is called VO Areas. The rest are irrelevant.

I want the following to run on Property Numbering:

            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            Range("C13").Select
            Selection.ClearContents
            Range("C8").Select
            Selection.ClearContents
            Range("B2").Select
            Selection.ClearContents
            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Range("B2").Select

I want the following to run on VO Areas:

            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            Range("C4").Value = "'Choose P"
            Range("C4").Select

All unspecified sheets should run:

sh.Protect UserInterFaceOnly:=True