User form trouble

  • Thread starter Thread starter Patty
  • Start date Start date
P

Patty

I have created 2 macros in Module form. A co-worker turned
me on to creating a "user form" to select the correct
macro to run based on information selected in the combobox
drop bown window.

My question is how do I get the userform to reconize the
combobox info as a seperate macro to run from my Macro
module? Do I use the "If/then run or goto statment" I am
at a loss on this. can anyone help.

TIA
Patty
 
Patty,

Not sure if I totally understand but I will take a stab.

Firstly, put the macros in a general module. Don't make them Private.

Then, for the combobox click event use some code like

Select Case Combobox1.ListIndex
Case 1: macro1()
Case 2: macro2()
etc.
End Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I am not sure if I follow.

If my "Combobox" is called "Formulas" and my
"Command buttons" are "Calculate" and "Cancel"
The two formulas are called "Sub FeetPerRoll () and Sub
ImpressionsPerRoll ()"

When th ComboBox is equal to Feet Per Roll I want to run
the FeetPerRoll () macro and so on.

Also I created the macros in module form so they are only
active if this spreadsheet is open, so would'ent the
module be public to start with or does it have to be
specified that way.

I thank you again.
Patty
 
Patty,

This is what I think you want. The 2 macros can be in the form module or in
a general module (my preference), but doing your code.

Private Sub Calculate_Click()

Select Case Formulas.ListIndex
Case 0: FeetPerRoll
Case 1: ImpresssionsPerRoll
End Select

End Sub

Private Sub UserForm_Activate()
With Formulas
.Clear
.AddItem "Feet Per Roll"
.AddItem "Impressions Per Roll"
.ListIndex = 0
End With
End Sub

Sub FeetPerRoll()
MsgBox "Feet Per Roll"
End Sub

Sub ImpresssionsPerRoll()
MsgBox "Impressions Per Roll"
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob

When I go to test this form below I get an Compile error
that states, "With objects must be user-defined type,
object or variant" OK/Help.

I looked in the help file and did not really see anything
that would help me.

Any suggestions.
Patty
 
Patty,

Bob's code assumes that the list box is named Formulas. Change that to the
name of your list box.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Back
Top