CODEWITHSUNDEEP
excelvba
Marco
Marco

Reputation: 35

Create drop down list in input-box

I'm creating a task list for our team, and to enter new tasks i've created input-boxes. There are only 5 departments that should be entered in the "department question" Te prevent people from entering a wrong department name, i would like to use that input-box a drop down list.

I've searched the net, but could not find how to create a drop down list in an input-box. I don't know if this is possible?

Can anyone help me?

The code i wrote for the inputs are as followed:

Private Sub Newaction_Click()
    Dim content As String, date1 As Date, date2 As Date, department As String
    Sheets("DO NOT DELETE").Rows("30:30").Copy
    Rows("14:14").Select
    Range("C14").Activate
    Selection.Insert Shift:=xlDown
    content = InputBox("describe the task")
    Range("C14").Value = content
    department = InputBox("to which department is the task assigned? ") '<-- here i want to create drop down list 
    Range("D14").Value = department
    date1 = InputBox("when does the task start")
    Range("F14").Value = date1
    date2 = InputBox("when should the task be finished? ")
    Range("G14").Value = date2
End Sub

Upvotes: 0

Views: 49929

Answers (2)

Stephen Rush
Stephen Rush

Reputation: 11

Inputbox with Type 8 can be used for drop-down manipulation. Here's some of that code:

'To select the table
Range(Selection.End(xlDown), Selection).Select

'To highlight the first cell
ActiveCell.Activate

'To store first box of contents in range
stove = ActiveCell

'InputBox Type:=8 allows cursor movement when used in combination with Set.
    recipe = "Select a name or press 'Cancel'."
    On Error GoTo 0
        Set table = Application.InputBox(Prompt:=recipe, Default:=stove, Type:=8)
        If vbCancel Then GoTo foodisready
    On Error Resume Next

This was originally created with each cell as a range name.

Upvotes: 0

Marco
Marco

Reputation: 35

I have created a form in excel in stead of using input box. For the selection of department i created a combo-box with the correct departments:

Private Sub Newaction_Click()

    Sheets("DO NOT DELETE").Rows("30:30").Copy
    Rows("14:14").Select
    Range("C14").Activate
    Selection.Insert Shift:=xlDown
    Cells(14, 3) = Taskd.Value
    Cells(14, 5) = ComboBox1
    Unload Me
    UserForm2.Show

End Sub

Private Sub UserForm_Initialize()

Taskd.Value = ""

    With ComboBox1
        .AddItem "Lean"
        .AddItem "Maintenance"
        .AddItem "Process Engineering"
        .AddItem "Safety"
        .AddItem "Workinstructions"
    End With

End Sub

For the dates i created a separate form (userfrom2 and userform3) to enter the dates on a calander. 

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

    On Error Resume Next
    startd = DateClicked
    Cells(14, 6).Value = startd
    Unload Me
    UserForm3.Show

End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

    On Error Resume Next
    endd = DateClicked
    Cells(14, 7).Value = endd
    Unload Me

End Sub

The Monthview1 is an extra option in Excel which you can activate via: forms toolbox --> right click on toolbox --> select Additional controls --> Microsoft Monthviews control

Upvotes: 1

Related Questions