Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to solicit input then run a macro?
I have to solicit two pieces of information (both pieces will be selections
from a small list) and then run a macro. I'd prefer to make this obvious instead of doing the crtl-key to run the macro, because an inexperienced user will be running it. What is the best way to do this? Should I create a form? Can I customize the form (colors? pictures? buttons?). Other ways? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to solicit input then run a macro?
use the Inputbox method, set the variable and then use it afterwards.
Example: myvar=InputBox("Please Select ....") -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "fedude" wrote: I have to solicit two pieces of information (both pieces will be selections from a small list) and then run a macro. I'd prefer to make this obvious instead of doing the crtl-key to run the macro, because an inexperienced user will be running it. What is the best way to do this? Should I create a form? Can I customize the form (colors? pictures? buttons?). Other ways? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to solicit input then run a macro?
Hi
Make a Command Button (on your sheet) from the Control Toolbox menu. Right click on the Command Button and click on wiew code. In the event code window enter this code, and maybe add a check routine to verify that the user have user has made the required selections before your macro is called. Private Sub CommandButton1_Click() Call MyMacro End Sub Regards, Per "fedude" skrev i meddelelsen ... I have to solicit two pieces of information (both pieces will be selections from a small list) and then run a macro. I'd prefer to make this obvious instead of doing the crtl-key to run the macro, because an inexperienced user will be running it. What is the best way to do this? Should I create a form? Can I customize the form (colors? pictures? buttons?). Other ways? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to solicit input then run a macro?
'Sub showForm() 'Put this into a standard module
'UserForm1.Show 'End Sub 'Put this into Thisworkbook module 'Private Sub Workbook_Open() 'Run "showForm" 'End Sub 'Add two comboboxes and a button to 'userform1 Private Sub CommandButton1_Click() 'Do your stuff MsgBox ComboBox1.Text & " " & ComboBox2.Text End Sub Private Sub UserForm_Initialize() Const combobox1ListColumn = "A" 'Change to your needs Const combobox2ListColumn = "B" 'Change to your needs Dim cellPointer As Variant lastRow = Range(combobox1ListColumn & Rows.Count).End(xlUp).Row looper = 1 'Starting row of data to fill combox1 ComboBox1.Text = Range(combobox1ListColumn & looper) Do While looper <= lastRow Set cellPointer = Range(combobox1ListColumn & looper) ComboBox1.AddItem (cellPointer) looper = looper + 1 Loop 'reset lastrow & looper lastRow = Range(combobox2ListColumn & Rows.Count).End(xlUp).Row looper = 1 'Starting row of data to fill combox2 ComboBox2.Text = Range(combobox2ListColumn & looper) Do While looper <= lastRow Set cellPointer = Range(combobox2ListColumn & looper) ComboBox2.AddItem (cellPointer) looper = looper + 1 Loop End Sub "fedude" wrote: I have to solicit two pieces of information (both pieces will be selections from a small list) and then run a macro. I'd prefer to make this obvious instead of doing the crtl-key to run the macro, because an inexperienced user will be running it. What is the best way to do this? Should I create a form? Can I customize the form (colors? pictures? buttons?). Other ways? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to solicit input then run a macro?
Thanks Mike,
I'm still a little confused about what to call my sub if I want to initialize ListBox1 control. Here is my code: Sub UserForm_Initialize() Dim looper As Integer looper = 1 'Starting number to fill listbox Do While looper <= 16 ListBox1.AddItem (looper) looper = looper + 1 Loop End Sub Also, in my tests, I loaded the listbox from a static range and it appeared fine. I could see the rows and could select the proper row. I used the up/down arrow buttons to position myself to a specific row, but I had to actually go into the contents window and manually select the row in order for the listbox control to select that data. Is there any way (or another control) that will allow me to just select the row in the list by simply using the up/down arrows "Mike" wrote: 'Sub showForm() 'Put this into a standard module 'UserForm1.Show 'End Sub 'Put this into Thisworkbook module 'Private Sub Workbook_Open() 'Run "showForm" 'End Sub 'Add two comboboxes and a button to 'userform1 Private Sub CommandButton1_Click() 'Do your stuff MsgBox ComboBox1.Text & " " & ComboBox2.Text End Sub Private Sub UserForm_Initialize() Const combobox1ListColumn = "A" 'Change to your needs Const combobox2ListColumn = "B" 'Change to your needs Dim cellPointer As Variant lastRow = Range(combobox1ListColumn & Rows.Count).End(xlUp).Row looper = 1 'Starting row of data to fill combox1 ComboBox1.Text = Range(combobox1ListColumn & looper) Do While looper <= lastRow Set cellPointer = Range(combobox1ListColumn & looper) ComboBox1.AddItem (cellPointer) looper = looper + 1 Loop 'reset lastrow & looper lastRow = Range(combobox2ListColumn & Rows.Count).End(xlUp).Row looper = 1 'Starting row of data to fill combox2 ComboBox2.Text = Range(combobox2ListColumn & looper) Do While looper <= lastRow Set cellPointer = Range(combobox2ListColumn & looper) ComboBox2.AddItem (cellPointer) looper = looper + 1 Loop End Sub "fedude" wrote: I have to solicit two pieces of information (both pieces will be selections from a small list) and then run a macro. I'd prefer to make this obvious instead of doing the crtl-key to run the macro, because an inexperienced user will be running it. What is the best way to do this? Should I create a form? Can I customize the form (colors? pictures? buttons?). Other ways? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
Input Box within a macro | Excel Discussion (Misc queries) | |||
Getting input within macro without the input box | Excel Programming | |||
input box within macro | Excel Worksheet Functions | |||
Macro asking for input | Excel Programming |