Using code to adjust scrolling area on sheet

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Using code to adjust scrolling area on sheet

Post by ABabeNChrist »

I use the following code below that limits the scrolling on a worksheet. This helps protect restricted areas of the sheet from being accidently deleted. But when I pre set the sheet scrolling area to fit the desired area, and If I were to insert an additional row there is that possibility of some rows with text can go beyond scrolling area. So if any changes had to be made the user would be unable. Is there a way that the sheet scrolling area can adjust with changes made from above scroll area.

Code: Select all

Private Sub Worksheet_Activate()

	Sheet1.ScrollArea = "A1:L20"

	'To set back to normal use:

	‘Sheet1.ScrollArea = ""

End Sub

User avatar
HansV
Administrator
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using code to adjust scrolling area on sheet

Post by HansV »

Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Using code to adjust scrolling area on sheet

Post by Don Wells »

I would try setting A1:L20 to a named range -- lets say "Scroll_Area". Then:
Change the line
Sheet1.ScrollArea = "A1:L20"

To
Sheet1.ScrollArea = Scroll_Area
Regards
Don

User avatar
HansV
Administrator
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using code to adjust scrolling area on sheet

Post by HansV »

Don't you mean

Sheet1.ScrollArea = "Scroll_Area"
Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Using code to adjust scrolling area on sheet

Post by Don Wells »

HansV wrote:Don't you mean

Sheet1.ScrollArea = "Scroll_Area"
I do indeed; nice catch.
Regards
Don

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using code to adjust scrolling area on sheet

Post by ABabeNChrist »

Thank you HansV and Don
just what I was looking for