Change ComboBox Value on Cell Change

[mr0range]

Hi,

I'm trying to setup a simple macro that will change the value of a combobox when the cell next to it is changed by the user (the simple combobox, not the one created using UserForms). This code here worked for a second - it set the value of the ComboBox back to 1, but then the ComboBox was stuck and would not change values if a new item was selected. Then it just stopped running. Can anyone make a suggestion as to what I'm doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("B9")) Is Nothing Then
Exit Sub
Else
ActiveSheet.Shapes("Combo1").ControlFormat.Value = 1
End If
Application.EnableEvents = False
End Sub

[MickG]

Hi, With out trying you code, it looks like you have the "EnableEvents" round the wrong way The code is now set to "False".
You need to run a bit of code with The "EnableEvents" set to "True",
and alter your code.
Mick