Hi there,

What is the in VBA for Dropdown list for user input?

I have a code like this: Inputbox("enter name of month : ")

Instead, I want to have it as Dropdown so it will reduce user input error.
And I also want to items in the dropdown list hard coded into the codes
instead of picking up from any cell ranges or any where else.

Thanks for any help.

Neon520

The best way to do this is to design a userform and add a code to
userform initilalize event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "January"
ComboBox1.AddItem "February"
ComboBox1.AddItem "March"
ComboBox1.AddItem "April"
ComboBox1.AddItem "May"
ComboBox1.AddItem "June"
ComboBox1.AddItem "July"
ComboBox1.AddItem "August"
ComboBox1.AddItem "September"
ComboBox1.AddItem "Ocotber"
ComboBox1.AddItem "November"
ComboBox1.AddItem "December"
End

On 21 Gru, 21:32, Neon520 wrote:
Hi there,

What is the in VBA for Dropdown list for user input?

I have a code like this: Inputbox("enter name of month : ")

Instead, I want to have it as Dropdown so it will reduce user input error.. *
And I also want to items in the dropdown list hard coded into the codes
instead of picking up from any cell ranges or any where else.

Thanks for any help.

Neon520

Hi Coder1215,

Thank you for your reply.

I tried pasting your code to mine, but it doesn't seem to work.

Here is the code I have and all I want is to replace
Mymonth = InputBox("Enter Name of Month (ALL CAPS): ")
with a dropdown list instead to reduce typo and user input error.


Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Mymonth = InputBox("Enter Name of Month (ALL CAPS): ")

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "Users:Neon:Desktop:TEST FOLDER:"
FName = Dir(Folder, MacID("XLS8"))

'MsgBox ("Found file:" & FName)
Newrowcount = 2
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
'MsgBox ("check Sheet : " & Sht.Name)
With Sht
Oldrowcount = 7
Do While .Range("B" & Oldrowcount) < ""
If UCase(.Range("B" & Oldrowcount)) = Mymonth Then
'Range("B7:B38").Copy
'Range("D1").PasteSpecial Paste:=xlPasteValues
.Rows(Oldrowcount).Copy _
Destination:=NewSht.Rows(Newrowcount)
'NewSht.Range("A" & Newrowcount) = .Range("A" & Oldrowcount)
'NewSht.Range("B" & Newrowcount) = .Range("B" & Oldrowcount)
'NewSht.Range("C" & Newrowcount) = .Range("C" & Oldrowcount)
'NewSht.Range("D" & Newrowcount) = .Range("D" & Oldrowcount)
Newrowcount = Newrowcount + 1
End If
Oldrowcount = Oldrowcount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
'MsgBox ("Found file : " & FName)
Loop

End Sub

FYI, I'm using Office 2004 for Mac, the file directory is slightly different
from Office for Window.

Thank you,
Neon520

"Coder1215" wrote:

The best way to do this is to design a userform and add a code to
userform initilalize event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "January"
ComboBox1.AddItem "February"
ComboBox1.AddItem "March"
ComboBox1.AddItem "April"
ComboBox1.AddItem "May"
ComboBox1.AddItem "June"
ComboBox1.AddItem "July"
ComboBox1.AddItem "August"
ComboBox1.AddItem "September"
ComboBox1.AddItem "Ocotber"
ComboBox1.AddItem "November"
ComboBox1.AddItem "December"
End

On 21 Gru, 21:32, Neon520 wrote:
Hi there,

What is the in VBA for Dropdown list for user input?

I have a code like this: Inputbox("enter name of month : ")

Instead, I want to have it as Dropdown so it will reduce user input error..
And I also want to items in the dropdown list hard coded into the codes
instead of picking up from any cell ranges or any where else.

Thanks for any help.

Neon520