Dynamic cell address resolution in visual basic application?

[Guest]

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.

 

[Bernie Deitrick]

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