Combo Box Trigger for Macro using Worksheet_SelectionChange

[Frank Hayes]

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

 

[mvanwaas]

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 /

 

[Frank Hayes]

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

Frank