Create drop down list in input-box

Question

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

Peter Albert · Accepted Answer · 2015-07-23 12:49:02Z

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

fretje · Accepted Answer · 2021-03-06 14:01:58Z

0

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.

edited Mar 6, 2021 at 14:01

fretje

8,3722 gold badges52 silver badges61 bronze badges

answered Mar 2, 2021 at 18:57

Stephen Rush

113 bronze badges

Collectives™ on Stack Overflow

Create drop down list in input-box

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related