I am attempting to trigger a macro ("Macro1") whenever the results of a
Combo Box change. I do not want to use Worksheet_Calculate() because I
wind up in an endless loop due to other design constraints on the worksheet.
The ComboBox uses cell B2 as the linked cell for the result. I have tried
the following code without success:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Macro1
End If
End Sub

I have also tried setting cell B1 to "=B2" and then triggering on the change
of B1, but that has also failed. Can anyone give some guidance on how I
might trigger the macro other than through Worksheet_Calculate() ?

I am using Excel 2000 SP-3

Thank you,

Frank Hayes

Is there some reason you're not using the much more direct
ComboBox*_Change() event? It's available for the ComboBox accessible
in the Control Toolbox Toolbar and is designed for exactly what I
think you're looking for.

/ Tyla /


On Mar 18, 7:43 am, "Frank Hayes" <haye...@verizon.net> wrote:
> I am attempting to trigger a macro ("Macro1") whenever the results of a
> Combo Box change. I do not want to use Worksheet_Calculate() because I
> wind up in an endless loop due to other design constraints on the worksheet.
> The ComboBox uses cell B2 as the linked cell for the result. I have tried
> the following code without success:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Target, Range("B2")) Is Nothing Then
> Macro1
> End If
> End Sub
>
> I have also tried setting cell B1 to "=B2" and then triggering on the change
> of B1, but that has also failed. Can anyone give some guidance on how I
> might trigger the macro other than through Worksheet_Calculate() ?
>
> I am using Excel 2000 SP-3
>
> Thank you,
>
> Frank Hayes

Worked great. Thank you. I just didn't know it was there!

Frank

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Is there some reason you're not using the much more direct
> ComboBox*_Change() event? It's available for the ComboBox accessible
> in the Control Toolbox Toolbar and is designed for exactly what I
> think you're looking for.
>
> / Tyla /
>
>
> On Mar 18, 7:43 am, "Frank Hayes" <haye...@verizon.net> wrote:
>> I am attempting to trigger a macro ("Macro1") whenever the results of a
>> Combo Box change. I do not want to use Worksheet_Calculate() because I
>> wind up in an endless loop due to other design constraints on the
>> worksheet.
>> The ComboBox uses cell B2 as the linked cell for the result. I have
>> tried
>> the following code without success:
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> If Not Intersect(Target, Range("B2")) Is Nothing Then
>> Macro1
>> End If
>> End Sub
>>
>> I have also tried setting cell B1 to "=B2" and then triggering on the
>> change
>> of B1, but that has also failed. Can anyone give some guidance on how I
>> might trigger the macro other than through Worksheet_Calculate() ?
>>
>> I am using Excel 2000 SP-3
>>
>> Thank you,
>>
>> Frank Hayes
>
>