Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003: Having trouble with conditional formatting with custom user function (Repost) | Excel Worksheet Functions | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
Trouble with saving user input in an Excel Form. | Excel Discussion (Misc queries) | |||
user form | Excel Programming | |||
I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming |