Worksheet activate not happening when workbook first opens

[ElvisSteel]

Hi,

I have a workbook where the sheets are all protected and I want to stop users scrolling up or across beyond the limits of the input areas.
I have used the following code (or variations of)...

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G32"
End Sub

This works fine except that when I first open the workbook, the first sheet displayed can still be scrolled. As soon as you move to another sheet and then back again, it is then OK.

It appears that the Worksheet_Activate code does not execute when the workbook is first opened.

How do I get round this?

Thanks

 

[Kaps_mr2]

you need to put it in the workbook events - specifically Workbook_Open(). thks

Kaps

 

[Andrew Poulsom]

Have you tried using the Workbook_Open event procedure? Example:

Private Sub Workbook_Open()
    Worksheets("Sheet1").ScrollArea = "A1:G32"
End Sub

 

[ElvisSteel]

What code do I need in the Workbook_Open event in order to achieve the same as "Me.ScrollArea" within the workbook code?

 

[ElvisSteel]

Andrew,

Thanks very much, your reply arrived just after I sent the next question.

Thread closed now

 

[Jonmo1]

Try

Private Sub Workbook_Open()
    ActiveSheet.ScrollArea = "A1:G32"
End Sub

 

[ElvisSteel]

Andrew,

Sorry cheating by using this thread for a separate question but could you use your expertise to look at another thread of mine that has had no response...

If you search for my posts "ElvisSteel", it is the one entitled "Ctrl Page Down in Protected sheet"

Thanks

 

[RoryA]

If the scroll area is the same each time, why don't you just set it and then save the workbook?

 

[Jonmo1]

If the scroll area is the same each time, why don't you just set it and then save the workbook?

I just confirmed this to be sure...

The Scroll Area setting is NOT saved when the book is reopened.

At least in XL2003 it is not.

 

[Andrew Poulsom]

If the scroll area is the same each time, why don't you just set it and then save the workbook?

The ScrollArea property isn't saved with the workbook is it?