Dynamic cell address resolution in visual basic application?

I am attempting to create an excel worksheet where when a user clicks on a
button (located in a cell), the worksheet will scroll to a pre-determined
location in the worksheet. My problem is that the only means I can find of
specifying the location to scroll is by supplying a static cell address. This
is problematic when a row is inserted in the spreadsheet, as I am attempting
to use this button; click; scroll convention in numerous places in the
spreadsheet, thus it creates a maintenance nightmare trying to keep all of
the addresses current.

Below is a sample of the VB code associated with the worksheet:
Private Sub CommandButton29_Click()
Application.Goto Reference:=Worksheets("Map").Range("A95"), Scroll:=True
End Sub

Any suggestions as to how I can modify this code so that the "A95" address
will dynamically be updated should a row be inserted in the worksheet that
would cause the actual location of the cell to move, would be greatly
appreciated.

Name cell A95 something, like "AMapCell" and change your code to

Application.Goto Reference:=Worksheets("Map").Range("AMapCell"),

Then it won't matter....

HTH,
Bernie
MS Excel MVP


"John Carter" <[email protected]> wrote in message
news:[email protected]...
>I am attempting to create an excel worksheet where when a user clicks on a
> button (located in a cell), the worksheet will scroll to a pre-determined
> location in the worksheet. My problem is that the only means I can find of
> specifying the location to scroll is by supplying a static cell address. This
> is problematic when a row is inserted in the spreadsheet, as I am attempting
> to use this button; click; scroll convention in numerous places in the
> spreadsheet, thus it creates a maintenance nightmare trying to keep all of
> the addresses current.
>
> Below is a sample of the VB code associated with the worksheet:
> Private Sub CommandButton29_Click()
> Application.Goto Reference:=Worksheets("Map").Range("A95"), Scroll:=True
> End Sub
>
> Any suggestions as to how I can modify this code so that the "A95" address
> will dynamically be updated should a row be inserted in the worksheet that
> would cause the actual location of the cell to move, would be greatly
> appreciated.
>