|
Option box for running a macro
I have 2 macros which I would like to combine into one. Basically I would
like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance. |
Option box for running a macro
You could use a Messagebox (Msgbox) for that.
But you only have predefined buttons for that like: yes no yes no cancel ok ok cancel and so on. if this is what you are looking for, use: dim Answer_Msgbox as Variant Answer_Msgbox = Msgbox "your Question", vbyesno, "your title" if Answer_Msgbox = vbyes then 'your Macro1 else 'your Macro2 end if if you don't like that, you could still make a userform, that looks like a msgbox and you can put your macros directly on the buttons. hth Carlo On Nov 27, 1:16 pm, Ant wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance. |
Option box for running a macro
The simplest method would be the input box.
Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance. |
Option box for running a macro
Thanks for your reply. Do you know if it is possible to have a box with the
selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance. |
Option box for running a macro
In that case you should consider to build your own userform.
(which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text - |
Option box for running a macro
I am almost there... I have created the UserForm with two option buttons for
the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text - |
Option box for running a macro
I don't recommend to put the code in Userform_Click,
it triggers also, if the user clicks somewhere else on the form. Make a Button, that's easier to understand. And put the Code directly on the button. To connect the two optionbuttons you have to put them into a frame control. that way only one of the optionbuttons can be true. Your sub looks actually fine, so I don't know why it doesn't work. I changed minor details: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- (you don't need the UserForm.show, because the form is already visible) Make the option1 default true, that way you prevent the user from clicking the button without selecting anything. Give me some feedback if it works or not. hth Carlo On Nov 28, 11:04 am, Ant wrote: I am almost there... I have created the UserForm with two option buttons for the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Option box for running a macro
I forgot something:
'------------------------------------------------------------------------- Private Sub CommandButton1_Click() If Me.OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- the Me is usually needed, to tell Excel where the Button is located cheers Carlo On Nov 28, 11:48 am, carlo wrote: I don't recommend to put the code in Userform_Click, it triggers also, if the user clicks somewhere else on the form. Make a Button, that's easier to understand. And put the Code directly on the button. To connect the two optionbuttons you have to put them into a frame control. that way only one of the optionbuttons can be true. Your sub looks actually fine, so I don't know why it doesn't work. I changed minor details: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- (you don't need the UserForm.show, because the form is already visible) Make the option1 default true, that way you prevent the user from clicking the button without selecting anything. Give me some feedback if it works or not. hth Carlo On Nov 28, 11:04 am, Ant wrote: I am almost there... I have created the UserForm with two option buttons for the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Option box for running a macro
Hi Carlo. I have it working now (although it didn't like the Me bits for some
reason). I added a commandbutton called "GO" on the userform and once the selection was made and the GO button pushed it ran the relative Macro. The initial Macro button had the UserForm.Show. I tried removing this as you mentioned but it seemed to require it. The call code sits under the commandbutton and the main code in seperate modules. One question though...How do you make the userform close once the Macro has started? Thanks for your help. Ant. "carlo" wrote: I forgot something: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If Me.OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- the Me is usually needed, to tell Excel where the Button is located cheers Carlo On Nov 28, 11:48 am, carlo wrote: I don't recommend to put the code in Userform_Click, it triggers also, if the user clicks somewhere else on the form. Make a Button, that's easier to understand. And put the Code directly on the button. To connect the two optionbuttons you have to put them into a frame control. that way only one of the optionbuttons can be true. Your sub looks actually fine, so I don't know why it doesn't work. I changed minor details: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- (you don't need the UserForm.show, because the form is already visible) Make the option1 default true, that way you prevent the user from clicking the button without selecting anything. Give me some feedback if it works or not. hth Carlo On Nov 28, 11:04 am, Ant wrote: I am almost there... I have created the UserForm with two option buttons for the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Option box for running a macro
I have answered my own question...
userform1.hide - of course! "Ant" wrote: Hi Carlo. I have it working now (although it didn't like the Me bits for some reason). I added a commandbutton called "GO" on the userform and once the selection was made and the GO button pushed it ran the relative Macro. The initial Macro button had the UserForm.Show. I tried removing this as you mentioned but it seemed to require it. The call code sits under the commandbutton and the main code in seperate modules. One question though...How do you make the userform close once the Macro has started? Thanks for your help. Ant. "carlo" wrote: I forgot something: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If Me.OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- the Me is usually needed, to tell Excel where the Button is located cheers Carlo On Nov 28, 11:48 am, carlo wrote: I don't recommend to put the code in Userform_Click, it triggers also, if the user clicks somewhere else on the form. Make a Button, that's easier to understand. And put the Code directly on the button. To connect the two optionbuttons you have to put them into a frame control. that way only one of the optionbuttons can be true. Your sub looks actually fine, so I don't know why it doesn't work. I changed minor details: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- (you don't need the UserForm.show, because the form is already visible) Make the option1 default true, that way you prevent the user from clicking the button without selecting anything. Give me some feedback if it works or not. hth Carlo On Nov 28, 11:04 am, Ant wrote: I am almost there... I have created the UserForm with two option buttons for the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Option box for running a macro
you can do
userform1.hide unload userform1 or if you are on the form me.hide unload me the difference between unload and hide: unload takes the form out of the memory, so it will be initialised again when you open it the next time. hide just makes it invisble, all selection and entries will be stored. hth Carlo On Nov 28, 12:40 pm, Ant wrote: I have answered my own question... userform1.hide - of course! "Ant" wrote: Hi Carlo. I have it working now (although it didn't like the Me bits for some reason). I added a commandbutton called "GO" on the userform and once the selection was made and the GO button pushed it ran the relative Macro. The initial Macro button had the UserForm.Show. I tried removing this as you mentioned but it seemed to require it. The call code sits under the commandbutton and the main code in seperate modules. One question though...How do you make the userform close once the Macro has started? Thanks for your help. Ant. "carlo" wrote: I forgot something: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If Me.OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- the Me is usually needed, to tell Excel where the Button is located cheers Carlo On Nov 28, 11:48 am, carlo wrote: I don't recommend to put the code in Userform_Click, it triggers also, if the user clicks somewhere else on the form. Make a Button, that's easier to understand. And put the Code directly on the button. To connect the two optionbuttons you have to put them into a frame control. that way only one of the optionbuttons can be true. Your sub looks actually fine, so I don't know why it doesn't work. I changed minor details: '------------------------------------------------------------------------- Private Sub CommandButton1_Click() If OptionButton1.Value = True Then Call Macro1 Else Call Macro2 End If Unload Me End Sub '------------------------------------------------------------------------- (you don't need the UserForm.show, because the form is already visible) Make the option1 default true, that way you prevent the user from clicking the button without selecting anything. Give me some feedback if it works or not. hth Carlo On Nov 28, 11:04 am, Ant wrote: I am almost there... I have created the UserForm with two option buttons for the 2 macros. I seem to be missing the code which connects the selection of the option button and running of either of the macros. At the moment the UserForm comes up on screen, I select an option then the macro just stops. Can you see what I am missing? Also, I'm not sure where I should put the code. Do I have it within the UserForm as below, or should it be seperate? Private Sub UserForm_Click() UserForm1.Show If OptionButton1.Value = True Then Call Macro1 ElseIf OptionButton2.Value = True Then Call Macro2 Else Exit Sub End If End Sub "carlo" wrote: In that case you should consider to build your own userform. (which sounds harder than it is.) You should find some nice tutorials online. If you have any problems, just ask. Cheers Carlo On Nov 28, 5:14 am, Ant wrote: Thanks for your reply. Do you know if it is possible to have a box with the selection built in? Perhaps a drop down arrow with both choices available. This would allow the user to simply select an option without having to type in anything. "JLGWhiz" wrote: The simplest method would be the input box. Sub opt1or2() myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option", Type:=1) If myOpt = 1 Then Call Macro1 ElseIf myOpt = 2 Then Call Macro2 Else Exit Sub End If End Sub You can attach this to a button from the forms tool bar or the ToolBox tool bar. See VBA help for using the ToolBox button. "Ant" wrote: I have 2 macros which I would like to combine into one. Basically I would like the user to start the macro (push a button) and a window/balloon or something will pop up with the choice of option 1 or option 2. Choosing option 1 will run macro 1 and choosing option 2 will run macro 2. Thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 12:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com