Input Box with Drop down list

[BMAK]

Hi guys,

I am trying to create an input box that will provide the user with a drop down menu to select a month, I currently have an input box where the user types in the Month but would like to suppress this due to the likelihood of errors and also for useability. I just dont know where to start to get the drop down list have seen some code but that seems to take the information from a sheet in the excel file, i would like the months to be imbedded in the code.

Any help would be greatly appreciated, even a template of the code that shows the box with a dropdown list that i could modify for my own use.

Thanks,

Here is my current code:


Sub rpl()
Sheets("Dimensions").Select
Range("A1:E91").Select
What = Range("B6")
repl = InputBox("What Month would you like to view?")
If repl = "" Then
MsgBox ("No Month was entered")
Exit Sub
End If
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

[Jan Karel Pieterse]

You would have to create a userform with a combobox on it to do this, the Inputbox method does not have a dropdown.

[Mahahaava]

Hi,

I've attached an xls that has a rudimentary "month" pulldown. The Code is in Sheet 1 Code and in a Userform (Userform1).

This works by dblclicking $A$1 and returns the value of the month selected to same.

Just to get the idea. This is the code:

Sheet 1:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target(1, 1).Address = "$A$1" Then
Cancel = True
UserForm1.Show
End If
End Sub[/vba]
UserForm1:
[vba]
Private Sub ComboBox1_Click()
Unload UserForm1
End Sub

Private Sub ComboBox1_AfterUpdate()
Dim Month As String
Month = ComboBox1.Value
ActiveWorkbook.ActiveSheet.Cells(1, 1).Value = Month
End Sub
Private Sub Userform_Initialize()
Dim Months As Variant
ReDim Months(12)
Months = Array("January", "February", "March", "April", _
"May", "June", "July", "August", "September", "October", "November", "December")
ComboBox1.ColumnCount = 1
ComboBox1.List() = Months
End Sub
The UserForm only has one Control, ComboBox1

HTH.

Petri
11188

[BMAK]

Thanks soo much for your replies got the drop down combo box owrking a treat just had to do a tiny bit of editing to get it working for my sheet, Thanks again.

[david000]

There is a handy constant for this also.


Private Sub Userform_Initialize()
Dim i As Integer
For i = 1 To 12
ComboBox1.AddItem MonthName(i)
Next i
End Sub

[ashok1729]

Hi, But how to execute it? It's a Private Sub.

[Tom Jones]

Hi, But how to execute it? It's a Private Sub.

Double click in cell A1