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

Jandy,

Use named ranges.

HTH,
Bernie
MS Excel MVP


"Jandy" wrote in message
...
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

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" wrote in message
...
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