+ Reply to Thread
Results 1 to 3 of 3

Relative Cell References within VBA code

  1. #1
    Jandy
    Guest

    Relative Cell References within VBA code

    I am using Excel 2000 ActiveX Option Buttons from the Control Toolbar.
    Within the OptionButton code, I am using a statement like:
    Range ("B23") = OptionButton1.Caption
    to place the Option Button caption text into a specific cell in my
    worksheet. However if/when I insert or delete a Row or Column that relocates
    cell B23 (in this example), the VBA code cell-address is not updated to the
    new location. Is there a way to make the VBA cell reference relative so that
    the VBA code does not have to be updated each time a design change
    (Insert/Delete Row or Column) is made to the Worksheet?
    Any help will certainly be appreciated.
    Thank you

  2. #2
    Bernie Deitrick
    Guest

    Re: Relative Cell References within VBA code

    Jandy,

    Use named ranges.

    HTH,
    Bernie
    MS Excel MVP


    "Jandy" <[email protected]> wrote in message
    news:[email protected]...
    > I am using Excel 2000 ActiveX Option Buttons from the Control Toolbar.
    > Within the OptionButton code, I am using a statement like:
    > Range ("B23") = OptionButton1.Caption
    > to place the Option Button caption text into a specific cell in my
    > worksheet. However if/when I insert or delete a Row or Column that

    relocates
    > cell B23 (in this example), the VBA code cell-address is not updated to

    the
    > new location. Is there a way to make the VBA cell reference relative so

    that
    > the VBA code does not have to be updated each time a design change
    > (Insert/Delete Row or Column) is made to the Worksheet?
    > Any help will certainly be appreciated.
    > Thank you




  3. #3
    Jandy
    Guest

    Re: Relative Cell References within VBA code

    Bernie,
    Thank you very much. So simple and it worked fine. I appretiate the time
    you took to help.
    Jandy

    "Bernie Deitrick" wrote:

    > Jandy,
    >
    > Use named ranges.
    >
    > HTH,
    > Bernie
    > MS Excel MVP
    >
    >
    > "Jandy" <[email protected]> wrote in message
    > news:[email protected]...
    > > I am using Excel 2000 ActiveX Option Buttons from the Control Toolbar.
    > > Within the OptionButton code, I am using a statement like:
    > > Range ("B23") = OptionButton1.Caption
    > > to place the Option Button caption text into a specific cell in my
    > > worksheet. However if/when I insert or delete a Row or Column that

    > relocates
    > > cell B23 (in this example), the VBA code cell-address is not updated to

    > the
    > > new location. Is there a way to make the VBA cell reference relative so

    > that
    > > the VBA code does not have to be updated each time a design change
    > > (Insert/Delete Row or Column) is made to the Worksheet?
    > > Any help will certainly be appreciated.
    > > Thank you

    >
    >
    >


+ 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