Creating a scroll area in excel

[apar93]

Can anyone help me create a scroll area in excel 2011?
I've been reading the steps,

• From the Developer tab, in the Control group click Properties
• Locate Scroll Area and enter the range you want to limit the worksheet to. Such as A1:Q30

However, in excel 2011 I do not see such button "Properties" in the developer tab.

Help!!

 

[JoeMo]

For Windows that button should be in the Controls section of the Developer tab. Next to the Design Mode button. When you click it a dialog box will be displayed with Scroll Area near the bottom and a space next to it where you can enter the address (A1:Q30).

 

[apar93]

I'm working on a mac.

 

[JoeMo]

Sorry, I don't use a Mac.

 

[apar93]

Thanks.

 

[apar93]

Hi Joe,
I was able to find a pc to work with. I followed the steps you gave however whenever I save the sheet as a macro enabled file, close it, and then try to reopen the sheet the range does not save. Do I have to set the scroll range every time I open the sheet, or is there a way to make this a permanent add-on?

 

[JoeMo]

Hi Joe,
I was able to find a pc to work with. I followed the steps you gave however whenever I save the sheet as a macro enabled file, close it, and then try to reopen the sheet the range does not save. Do I have to set the scroll range every time I open the sheet, or is there a way to make this a permanent add-on?

You can try adding a worksheet_activate event code to simulate what you get by manually setting the scroll area. Maybe like this:
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Range("A1:Q30")
    .Locked = False
    .FormulaHidden = False
    Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Me.EnableSelection = xlUnlockedCells
End With
Application.ScreenUpdating = True
End Sub

 

[apar93]

Thank your for the suggestion. However, I just tried this and it does not work
The page continues to scroll past the assigned range.

 

[JoeMo]

Sorry, I misunderstood what you want to achieve. Try this instead:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Me.ScrollArea = Me.Range("A1:Q30").Address
Application.ScreenUpdating = True
End Sub

 

[apar93]

Again, thank you for your help. However, this doesn't seem to work either