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?

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?

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?

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?

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?