Novice in need of lesson and help

bipper

New Member
Joined
Sep 13, 2005
Messages
10
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.

Code:
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

try a listbox
check out the properties: rowsource

you can "feed" the controls when the userform is initialized (or activated)
Code:
ListBox1.RowSource = Sheets(1).Range("A1:A10")
this can be made dynamic (check out some threads with "lastrow")

kind regards,
Erik
 

bipper

New Member
Joined
Sep 13, 2005
Messages
10
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
Private Sub CommandButton1_Click()
UserForm1.TextBox1 = ListBox1
Unload Me
End Sub
 

bipper

New Member
Joined
Sep 13, 2005
Messages
10
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.
 

Forum statistics

Threads
1,141,018
Messages
5,703,757
Members
421,314
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top