PDA

View Full Version : call a sub from a combo box



Tezzies
11-18-2007, 11:44 AM
Hello all,

I am new programmer so sorry if this is a really stupid question.

I wish to call a sub, the name of which is held in a combo box.

Call combobox.text / .value ect ect doesn?t work

I tried placing the value in a string and calling it, but it doesn?t like that either. I know I can use "select" then "case" all the different options in the combo box if need be, but that would be cheating and I have a bet with someone that it can be done.

Many thanks in advance

XLGibbs
11-18-2007, 11:49 AM
A recent topic..discussion at the link. Should be just what the doctor ordered for your request. Post back if you have issues applying it or problems within your combo box code..

http://www.vbaexpress.com/forum/showthread.php?t=16068&highlight=call+procedure+click

mikerickson
11-18-2007, 12:10 PM
The syntax for getting the string in the combobox will depend on what kind of combo box, but this will call the routine whose name is selected in ComboBox1.


Application.Run ComboBox1.Value

Tezzies
11-18-2007, 12:23 PM
Thanks for such a quick reply

not sure that’s quite what I am after, below is a simple version of my code. I am hoping to be able to add lines to my combo box which will call other subs when selected. Plus if I am viewing the form I could type in msg3 to call that sub.

Private Sub UserForm_Activate()
ComboBox1.AddItem "msg1"
ComboBox1.AddItem "msg2"
End Sub

Private Sub dostuff_Click()
Call ComboBox1.Value ---------- problem area
End Sub


Sub msg1()
MsgBox "you called msg1"
End Sub


Sub msg2()
MsgBox "you called msg2"
End Sub

Sub msg3()
MsgBox "you called msg3 the hidden sub"
End Sub

Bob Phillips
11-18-2007, 12:38 PM
Try



Application.Run Combobox1.Value

Tezzies
11-18-2007, 01:02 PM
application.run doesnt work either, gives an error saying macro cannot be found or are not enabled, but i know they are. Could it be down to me using office 2007 and it being totallly pants.

Norie
11-18-2007, 01:14 PM
Where are the subs located?

Tezzies
11-18-2007, 01:17 PM
all the code is on the userform

XLGibbs
11-18-2007, 01:18 PM
application.run doesnt work either, gives an error saying macro cannot be found or are not enabled, but i know they are. Could it be down to me using office 2007 and it being totallly pants.

If the macro is not in the active workbook, you would need to specify the workbook.

Application.Run "Personal.xls!" & combobox1.Value

(or something like that)

Bob Phillips
11-18-2007, 03:25 PM
all the code is on the userform

Then move the macros to a standard code module, and then use Application.Run.