Dropdown list instead of Inputbox

[Neon520]

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

 

[Coder1215]

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

 

[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:Neonesktop: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