PDA

View Full Version : Limit Scroll area on open



inked
07-28-2006, 01:47 AM
I have the following simple code that limits the scroll area. However, upon opening the workbook, the scroll area is not limited until the user clicks on a cell. Anyway to make execute it on open?

Thanks.

-ep




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = "A1:BS46"
End Sub

johnske
07-28-2006, 02:14 AM
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:BS46"
End Sub

inked
07-28-2006, 02:37 AM
Still not working. When I open the workbook I can go directly to the scroll bar and scroll outside the limit. Am I doing something wrong?

-ep

asingh
07-28-2006, 03:48 AM
Give this a try......put the code in you workbook open event. If you want to execute on all the sheet..just loop through each sheet in you workbook.


Private Sub Workbook_Open()
Dim ws As Worksheet

Set ws = ActiveSheet

ws.ScrollArea = "A1:BS46"

Set ws = Nothing

End Sub

johnske
07-28-2006, 04:26 AM
If you're always opening it at the sheet you want to restrict the scroll area on use (in the ThisWorkbook module)
Option Explicit
Private Sub Workbook_Open()
ActiveSheet.ScrollArea = "A1:BS46"
End Sub
if you want the restricted area to apply to every worksheet use (in the ThisWorkbook module)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.ScrollArea = "A1:BS46"
End Sub

If you want it to apply to a specific worksheet use what I gave you previously in the worksheet module

deanrd
10-13-2006, 02:57 PM
This works well except for a couple of issues. I see a highlited/outlined box around the 1st cell in the area no matter what I do. If I create a custom view then scrollarea is disabled. I can't get rid of this box, any ideas? I've seen a link that works for forms in Access, but nothing for Excel. Is there any way to disable the scroll wheel in Excel(that would be the best)?

Thanx in advance - Dean