Help with small scroll lock code

[g_OUELLET]

Trying to set code for a scroll lock so we do not go past Colum E. Can go from A3:E203. But not past column "E" I get Run time Error 9" This is what I have.

Private Sub Workbook_Open()
Worksheets("RestaurantCharges").ScrollArea = "A3:E3"
End Sub

Deleted Image - It was blowing up the page & had actual personal information - Moderator

 

[Smitty]

There's nothing wrong with your code, so I'd suspect the sheet name. Does the "RestaurantCharges" sheet really have no space inbetween t/C? Note that when code refers to a sheet name it needs to be exact - even a leading or trailing space can throw it off.

HTH,

 

[Michael M]

Hi
You code works Ok for me, but I change dthe scroll area to meet your range

Private Sub Workbook_Open()
Worksheets("RestaurantCharges").ScrollArea = "A3:E203"
End Sub

 

[g_OUELLET]

Hi
You code works Ok for me, but I change dthe scroll area to meet your range

Private Sub Workbook_Open()
Worksheets("RestaurantCharges").ScrollArea = "A3:E203"
End Sub

I will try it thanks

 

[Michael M]

Did you note Smitty's comment about sheet names being EXACT.

 

[g_OUELLET]

I will try it thanks

I have 26 sheets in the work book. Is that why it will not work?
I have "Pay Period 1" to "Pay Period 26"

 

[Michael M]

The code you posted will only "LOCK" the "RestaurantCharges", nothing else.
Are you saying you want all sheets locked the same way.

 

[g_OUELLET]

The code you posted will only "LOCK" the "RestaurantCharges", nothing else.
Are you saying you want all sheets locked the same way.

Yes all sheets in the workbook. The file name is "Restaurant Charges" inside I have (1) tab "Employee setup" another tab "Sum Sheet" the rest are "Pay Period 1 Pay Period 2 etc.. to "Pay Period 26 these are the sheets i need the scroll lock.

 

[Michael M]

Try this

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Employee setup" And ws.Name <> "Sum Sheet" Then
        ws.ScrollArea = "A3:E203"
    End If
 Next
End Sub

 

[g_OUELLET]

Try this

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Employee setup" And ws.Name <> "Sum Sheet" Then
        ws.ScrollArea = "A3:E203"
    End If
 Next
End Sub

Thank you, Thank you, Thank you
Works great
Gilles