 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Option 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. 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 > 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. 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. > 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. 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. > The simplest method would be the input box. > [quoted text clipped - 19 lines] > > > > Thanks in advance. 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 > 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. [quoted text clipped - 26 lines] > > - Show quoted text - 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 > In that case you should consider to build your own userform. > (which sounds harder than it is.) [quoted text clipped - 36 lines] > > > > - Show quoted text - 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 > 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 [quoted text clipped - 56 lines] > > - Show quoted text - 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 > I don't recommend to put the code in Userform_Click, > it triggers also, if the user clicks somewhere else on [quoted text clipped - 94 lines] > > - Show quoted text - 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. > I forgot something: > [quoted text clipped - 111 lines] > > > > - Show quoted text - I have answered my own question... userform1.hide - of course! > 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 [quoted text clipped - 123 lines] > > > > > > - Show quoted text - 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 > I have answered my own question... > [quoted text clipped - 129 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.