How to restrict scroll area

[CyberBuzzard]

I have found Worksheets(1).ScrollArea to restrict viewing areas but it will
only work with static A1 style of referencing cells.

I required code to the effect that the scroll area is restricted but
dynamically, since I cannot know the limits of the area beforehand.

Any hel will be appreciated.

 

[Shawn O'Donnell]

I have found Worksheets(1).ScrollArea to restrict viewing areas but it will
only work with static A1 style of referencing cells.

I required code to the effect that the scroll area is restricted but
dynamically, since I cannot know the limits of the area beforehand.

Can't you dynamically generate an A1-style reference for the scroll area,
then pass it to ScrollArea?

Tell us what you've tried.

 

[CyberBuzzard]

I used ActiveCell.AddressLocal to obtain the Upper Left and Lower Right
cells of the area of interest.

Then I tried this:

FirstString = Assumptions.Range("Msg1").Text 'Contains first
address.
SecondString = Assumptions.Range("Msg2").Text 'Contains second address.
ThirdString = ":"
FourthString = """"
NewString = FourthString & Assumptions.Range("Msg1").Text & ThirdString
& _
Assumptions.Range("Msg2").Text & FourthString
Assumptions.Range("Msg2").Offset(2, 0) = NewString 'Enter in new cell.

Worksheets(1).ScrollArea = FourthString & Assumptions.Range("Msg1").Text
& ThirdString & _
Assumptions.Range("Msg2").Text & FourthString

I used a MsgBox to view the result and the text was fine but to no avail, I
always get the "Application-defined or object-defined error".

Can you see any errors in my code? I initially used the name of the
worksheet but also failed, though I'm sure that's not the source of the error
message.

 

[Tom Ogilvy]

Don't use addresslocal in VBA.
Rather than using string and concatenating, use the ranges themselves

Dim rng1 as Range, rng2 as Range, rng as Range
' code that determines upper left and activates it
set rng1 = ActiveCell
' code that determines lower right and activates cell
set rng2 = ActiveCell
set rng = Range(rng1,rng2)

Worksheets(1).ScrollArea = rng.Address

 

[Shawn O'Donnell]

Then I tried this:

<snip>

That almost would have worked--if you had used the .Address property of the
Range objects rather than the .Text property. .Text gives you the contents
of the cells. The other problem was that you don't need to put quotes on
strings you're constructing, unless you want the quote as part of the string.
You didn't want to do that here. When you're manually entering an A1-style
address, you need the quotes to tell the VB interpreter "this is a string,
don't interpret it as a variable name." When you pass an address in a String
variable, the interpreter won't evaluate the String further.

C2 = "A1"
Range(C2).Select

You'll be surprised which cell that will select if you don't read carefully.


In any case, do as Tom suggests and use the ranges themselves. So long as
you've figured out which cells to name Msg1 and Msg2, you could say:

Worksheets(1).ScrollArea = Range("Msg1", "Msg2").Address