Change event and calculate event

[Antje]

Hello,
I have a spreadsheet with nine option buttons from the Forms toolbar. All of
them are linked to the same cell, P6. I also have a chart that displays one
of nine data ranges (which are all stored on a separate sheet in the same
workbook), depending on which option button is selected.
The twist to this is that the data ranges can be one of two number formats,
plain number or percentage. Thus, I entered some code in VBA to change that
number format accordingly. I placed the code in the Worksheet_change event,
hoping that the changing of the value in the linked cell would trigger that
event. This did not occur. Then, I placed my code in the Worksheet_calculate
event, which worked. However, on certain actions, e.g. selecting an entire
row and deleting it, Excel hangs and I can only forcefully exit Excel.
Why does it do that? Any suggestions/solutions? Also, is there a way to
trigger the change event even though I am using Excel controls rather than
ActiveX controls?
Is there another way to accomplish that without having to use ActiveX
controls?
Apologies for the lengthy explanation.
Any help is greatly appreciated.
TIA,
BR,
Antje Crawford

[Jim Thomlinson]

You can call the change event the same as you would call any other procedure.
The only trick is that the argument is a range object "Target"

call worksheet_change(sheet1.range("A1"))

HTH

"Antje" wrote:

> Hello,
> I have a spreadsheet with nine option buttons from the Forms toolbar. All of
> them are linked to the same cell, P6. I also have a chart that displays one
> of nine data ranges (which are all stored on a separate sheet in the same
> workbook), depending on which option button is selected.
> The twist to this is that the data ranges can be one of two number formats,
> plain number or percentage. Thus, I entered some code in VBA to change that
> number format accordingly. I placed the code in the Worksheet_change event,
> hoping that the changing of the value in the linked cell would trigger that
> event. This did not occur. Then, I placed my code in the Worksheet_calculate
> event, which worked. However, on certain actions, e.g. selecting an entire
> row and deleting it, Excel hangs and I can only forcefully exit Excel.
> Why does it do that? Any suggestions/solutions? Also, is there a way to
> trigger the change event even though I am using Excel controls rather than
> ActiveX controls?
> Is there another way to accomplish that without having to use ActiveX
> controls?
> Apologies for the lengthy explanation.
> Any help is greatly appreciated.
> TIA,
> BR,
> Antje Crawford