User input into macro

[PeterTaylor]

Dear All,
I am using excel 2007. I wish allow a user to input data from within a macro that can be used in the macro. I have been using inputbox statement but I was wondering if you can have a "picklist". For example, I am converting units and would like to to present the user with a list options (1.225,1.397,4.459, etc) to choose from. Is this possible?
Regards
Peter

 

[sykes]

You could insert a simple userform into your project, with a listbox on it - populated either from a range in the workbook, or dynamically in the initialise event of the userform.

Insert a module into your project (if you haven't already)

Declare a variable in the module

Public num As Double

Place your calling procedure into the module:

Sub peter_code()
' all you previous code here....

UserForm1.Show
MsgBox "You chose " & num & Chr(10) & "The answer is " & num * 5

' the rest of your code here....

End Sub

Put the following code into the Userform's module:

Private Sub ListBox1_Change()
num = Me.ListBox1.Value
Unload UserForm1
End Sub


Private Sub UserForm_Initialize()
With Me.ListBox1
    .AddItem "1.225"
    .AddItem "1.397"
    .AddItem "4.459"
End With
End Sub

 

[PeterTaylor]

Thank You,
I appreciate the help.
Peter