How to run macros from combobox selection
 

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

How to run macros from combobox selection
 

"Benway" wrote:

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.

How to run macros from combobox selection
 

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

"Benway" wrote:

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.

How to run macros from combobox selection
 

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" wrote:

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

"Benway" wrote:

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.

How to run macros from combobox selection
 

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.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

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" wrote:

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

"Benway" wrote:

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.

How to run macros from combobox selection
 

Thanks, I'll give it a go.

"ryguy7272" wrote:

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.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

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" wrote:

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Benway" wrote:

"Benway" wrote:

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.