VBA Macro InputBox to display dropdown opposed to entering text?

[Johnnyboy1x]

Hello All --

I have a macro button in my spreadsheet that initiates various input boxes and places input in specific cells

I am running Excel 2016, WIN 7 64-bit

I know the line in my code below starting with DROPDOWNBOX is not right, but that is the best way I can express what I am trying to do.

Hopefully someone can help!

Thank you in advance, Johnny

Private Sub CommandButton1_Click()
Dim myValue As Variant
myValue = InputBox("Enter Serial Number (ex. 142110/16)")
Range("H13").Value = myValue
myValue = InputBox("Enter Your Name (ex. Bill Smith)")
Range("H15").Value = myValue
myValue = InputBox("Job Code  ex. PD,PM,FS,FLEX,WTY, INST")
DROPDOWNBOX Job Code = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")
Range("H14").Value = myValue
End Sub

 

[sericom]

Is this what you're after

ComboBox1.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")

 

[Johnnyboy1x]

Is this what you're after

ComboBox1.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")

Hello Sericom -

Thank you for the quick reply. I will say yes that might be what I need, but I can't make it work. run time 424 error.

Johnny

 

[AlphaFrog]

If the combobox has a range assigned to its RowSource property, clear the propery.

Or it could be cleared like this before assigning the array to the .List

[B]ComboBox1.RowSource = ""[/B]
ComboBox1.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")

Also, is your combobox called ComboBox1 ?

 

[Johnnyboy1x]

If the combobox has a range assigned to its RowSource property, clear the propery.

Or it could be cleared like this before assigning the array to the .List

[B]ComboBox1.RowSource = ""[/B]
ComboBox1.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")

Also, is your combobox called ComboBox1 ?

Thank you. I think my first issue is I create a combo box form control and not an active x.

That is the only change I made, no runtime error, but still not working for me. An input box does not display. The 3 text input boxes show up in the beginning lines of my code, but input box with drop down never does.

Thanks, John

 

[Johnnyboy1x]

Thank you. I think my first issue is I create a combo box form control and not an active x.

That is the only change I made, no runtime error, but still not working for me. An input box does not display. The 3 text input boxes show up in the beginning lines of my code, but input box with drop down never does.

Thanks, John

Just to recap...combobox1 is on my spreadsheet, it is functional and correctly displays the ARRAY values in the drop down. The first 2 input boxes show up, nothing shows up for ""myValue = ComboBox1.List .....". displays runtime error 13 type mismatch.

Thanks Again, Johnny

Private Sub CommandButton1_Click()
Dim myValue As Variant
myValue = InputBox("Enter Serial Number (ex. 142110/16)")
Range("H13").Value = myValue
myValue = InputBox("Enter Your Name (ex. Bill Smith)")
Range("H15").Value = myValue
myValue = ComboBox1.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")("Job Code  ex. PD,PM,FS,FLEX,WTY, INST")
Range("H14").Value = myValue
End Sub

 

[sericom]

Try this

Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat.List = Array("PD", "PM", "FS", "WTY", "FLX", "INST", "Non Billable")

Change "Sheet1" and "Drop Down1" if your names differ

 

[sericom]

Ah, you just want the selected item?

With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat
    myValue = .List(.ListIndex)
End With

 

[Johnnyboy1x]

Ah, you just want the selected item?

With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat
    myValue = .List(.ListIndex)
End With

Thank You. I want the input box to pop up, but instead of displaying a free text field to enter the value, I want it to display the drop down. Whatever is choosen will populate to the destination cell.

Thanks for all the effort. Its getting late here and perhaps I need to sleep on it!

Johnny

 

[sericom]

Pretty sure you can't have a dropdown on a input box. You may need to create a basic userform and call that.