How to run macros from combobox selection

[Benway]

Hello.
I've tried everything I can think of using the code below and cannot get
even MsgBox to show according to the selection. If anyone has a working
example, I'd be most appreciative!
Example one:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Addx As String
Dim Rng As Range

Addx = Target.Validation.Formula1
Set Rng = Range(Right(Addx, Len(Addx) - 1))

If Target.Address = "$A$1" Then
Select Case Target.Value
Case Is = Rng.Cells(1, 1) 'First Drop Down Item
'Call RandD
MsgBox "Macro 01"
Case Is = Rng.Cells(2, 1) 'Second Drop Down Item
'Call MacroB
MsgBox "Macro 02"
Case Is = Rng.Cells(3, 1) 'Third Drop Down Item
'Call MacroC
MsgBox "Macro 03"
End Select
End If

End Sub
============================
Example two:
Sub combo1_Change()
Select Case combo1.Value
Case "This"
MsgBox "This"
Case "That"
MsgBox "That"
Case "The Other Thing"
MsgBox "The Other Thing"
End Select
End Sub

 

[Benway]

:

I would add that example one uses a combo box that is "cell linked" to $A$1,
but Excel doesn't seem to recognize the combo box changing that cell as a
Worksheet change event and nothing happens.


Example two gives me an "object required" error on the first line.

 

[ryguy7272]

Right click the grey-space near one of your toolbars, and select Control
Toolbox. Then add a ComboBox. Right-click the sheet tab and paste this code
into the window that opens:

Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "This"
ComboBox1.AddItem "That"
ComboBox1.AddItem "The Other Thing"
ComboBox1.Text = ComboBox1.List(0)
End Sub

 

[Benway]

Thanks Ryan.
I put the code in, got out of design mode, saved, closed and reloaded the
sheet but the combobox is still empty...

 

[ryguy7272]

ya know, I tried the code and I could swear that it worked when I posted it,
but as I try it now, it doesn;t work. Ok look at the example at the bottom
of this page:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=233

The file is named 'multi col combo box.zip'

Hope that helps.

 

[Benway]

Thanks, I'll give it a go.

ya know, I tried the code and I could swear that it worked when I posted it,
but as I try it now, it doesn;t work. Ok look at the example at the bottom
of this page:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=233

The file is named 'multi col combo box.zip'

Hope that helps.