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]

"doesn't seem to work" is not a helpful description of what happens when you deselect then re-select the tab where you have installed the event code I posted in post #9 . Have you removed the earlier code from that sheet and followed the instructions I posted in post #7 for installing the code from post #9 ? If yes, can you explain exactly what doesn't work means? Since the code is for a worksheet activate event, it will not fire and take effect immediately after you install it. You must first deselect the worksheet then select it again. If it is the only worksheet in your workbook, then tell me that and I will modify the code to fire when you open the workbook.

 

[apar93]

I have followed the directions you gave in #7 and pasted the code from #9 as instructed. I saved as a macro enabled sheet. When I reopen the sheet I can continue scrolling past the range you set in the code. I do notice that I cannot click on cells outside the range specified in the code, although this is not what I am trying to accomplish. I have 3 sheets and only one sheet "Sheet1" needs the code applied. What do you mean I must first deselect the worksheet and then select it again?

 

[JoeMo]

Might be easier to create a workbook_open event module than to use the sheet activate event. Do the following:

First remove the existing activate event code from sheet1. Then follow the instructions below to install the open event code below the instructions.

To install ThisWorkbook code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. 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).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

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

 

[apar93]

Thank you for this, this worked. I cannot scroll past the range. However, it locks all cells outside of that range, therefore I cannot copy and paste a row or column...

 

[JoeMo]

Thank you for this, this worked. I cannot scroll past the range. However, it locks all cells outside of that range, therefore I cannot copy and paste a row or column...

That's the way scroll lock works. The only way to do anything outside the locked range is via VBA.

 

[apar93]

Meaning if I wanted to use the code to lock the range, I would need another code in order to copy and paste cells.

 

[JoeMo]

Yes.

 

[apar93]

Okay thanks.

 

[JoeMo]

You are welcome - thanks for the reply.