Novice in need of lesson and help

[bipper]

currenly I have the below in a macro I would like to replace the input box (labeled whos_range) with a drop down list to click on and expand the list as need, any help is greatly appreciated. This code is in a userform.
Also any code I post is modified from a search on this forum so thank you if you notice something you wrote.

Private Sub CatchAll_Click()
 Dim prompt, Whos_range
 Dim Prompt2, which_way
 Dim Prompt3, what_sheet
 Dim csh As Variant
 Dim psh As Variant
 Dim strWsToOpen As Variant
 prompt = "Update Who"
 Prompt2 = "copy to or copy from current workbook"
 Prompt3 = "what week you pullin"
 Dim cs As Worksheet 'copy sheet
 Dim ps As Worksheet 'paste sheet
     
Application.ScreenUpdating = False
 Whos_range = InputBox(prompt)
  If Whos_range = "" Then
    Response = MsgBox("Use a name so your sheet is not ruined", vbOKOnly & vbCritical, "Selection Error")
    Exit Sub
  End If
 which_way = InputBox(Prompt2)
  If which_way = "" Then
    Response = MsgBox("GOT TO KNOW A DIRECTION TO OR FROM", vbOKOnly & vbCritical, "Selection Error")
    Exit Sub
  End If
 what_sheet = InputBox(Prompt3)
  If what_sheet = "" Then
    Response = MsgBox("NEED TO HAVE A SHEET NAME OR IT BLOWS UP", vbOKOnly & vbCritical, "Selection Error")
    Exit Sub
  End If
    
strWsToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

  If strWsToOpen = False Then
    Response = MsgBox("Need to select a file", vbOKOnly & vbCritical, "Selection Error")
    Exit Sub
  End If

entire macro not included

 

[erik.van.geit]

Hi,

try a listbox
check out the properties: rowsource

you can "feed" the controls when the userform is initialized (or activated)

ListBox1.RowSource = Sheets(1).Range("A1:A10")

this can be made dynamic (check out some threads with "lastrow")

kind regards,
Erik

 

[bipper]

Thank you for your reply erik, however, as I stated I am a self tought novice and in order to understand your suggestion I need a little more explanation if possible? I understand the last entry part, the part I have a problem with is making a listbox or combobox appear during the macro. The input box works because someone posted the code about a year ago. I thought it would be a matter of changing inputbox to listbox or combobox, but I can not seem to get the proper command line for either box to appear. This is strictly for learning purposes I am curious that is all.

Thanks again and I appreciate your response already and I am still trying to get it to work.

Bip

 

[erik.van.geit]

my thought was you wanted to put the listbox on the form itself
first suggestion would be to do it that way

if you managed to do so, then you can try the following
put the listbox on another form

when hitting button on first userform, you can make the second one popup
UserForm2.Show

a button would get the value (in this example to a textbox on userform1)and close

Private Sub CommandButton1_Click()
UserForm1.TextBox1 = ListBox1
Unload Me
End Sub

 

[bipper]

I think I understand your suggestion push the info along when I select that particular instruction from the form. Thank you will try that too.

 

[erik.van.geit]

you're welcome !!

feel free to ask more if needed