Running a Macro based on Forms Combo Box

[mtampa]

Hi Guys,

I have a forms combo box that outputs to cell K3. When item 3 is selected and the value in cell K3 = 3, I want my macro called EMPVILL to run. However, it only fires when I type a 3 in the box and hit enter. As of now, when I change the value of the combo box to 1, 2, 3 or 4, nothing happens, it just sits there.

Here is the code I'm using:

rivate Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$K$3" Then
Select Case Target.Value
Case Is = 3: Call EMPVILL
Case Else: 'Do Nothing
End Select
End If
End Sub

Can someone point me in the right direction to make it fire when cell K3 changes to the value of 3?

Thanks,

Mike

 

[mtampa]

Hi All - I'm still struggling with this topic...does anyone have any solutions to my issue? If you need a copy of my worksheet, I'd be more then happy to send it to you.

Thanks,

Mike

 

[AlphaFrog]

Instead of a Worksheet_Change procedure, assign a macro to the Form combobox that tests the value of K3. The assigned macro is triggered each time you change the combobox.

Assign this to your combobox. Right-click on the combobox and select Assign Macro.

Sub Test_K3()
    If Range("K3").Value = 3 Then Call EMPVILL
End Sub

 

[mtampa]

Wow, making progress! It did it but now it runs the macro no matter what I select....option 1, 2 or 3 all trigger it.

 

[mtampa]

Wait...nevermind...I'm fixing something.

 

[AlphaFrog]

The macro Test_K3 is triggered every time you change the combobox, but it only should call EMPVILL when K3 equals 3.

EDIT: Just saw your never mind message.

 

[mtampa]

Actually I jumped the gun two times.

I thought I had an error but do not.

Yes, you are correct, the macro should only run when the combo box selects choice three but it is indeed running every time a selection is made. My idea of what was wrong proved to be a non-issue.

 

[AlphaFrog]

You're welcome.

 

[mtampa]

I think we're a bit off on our replies AlphaFrog....I haven't quite resolved it. If you could help me figure out why I runs every time any selection is made, I'd appreciate it.

Thanks,

Mike

 

[AlphaFrog]

Did you delete your Worksheet_Change procedure?