+ Reply to Thread
Results 1 to 2 of 2

Dynamic cell address resolution in visual basic application?

  1. #1
    John Carter
    Guest

    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.


  2. #2
    Bernie Deitrick
    Guest

    Re: Dynamic cell address resolution in visual basic application?

    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" <JohnCarter@discussions.microsoft.com> wrote in message
    news:BD8025F6-BFAE-47BE-A4C7-723F792AB42A@microsoft.com...
    >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.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1