User form trouble
 

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

User form trouble
 

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)

"Patty" wrote in message
...
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

User form trouble
 

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

-----Original Message-----
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)

"Patty" wrote in
message
...
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


.

User form trouble
 

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)

wrote in message
...
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

-----Original Message-----
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)

"Patty" wrote in
message
...
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


.

User form trouble
 

Thank you for your help and understanding on this Bob.

Patty

-----Original Message-----
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)

wrote in message
...
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

-----Original Message-----
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)

"Patty" wrote in
message
...
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


.



.

User form trouble
 

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


-----Original Message-----
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)

wrote in message
...
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

-----Original Message-----
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)

"Patty" wrote in
message
...
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


.



.

User form trouble
 

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


"Patty" wrote in message
...
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


-----Original Message-----
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)