VBA Userform dropdown menu execution

Question

I currently have this code which allows me to launch the userform, input the an item in the text box, auto populate the date, and select from a drop down menu then paste that information into a new row.

The cbm (combo-box) item draws its values from a separate dynamically expanding table and is a drop down menu on the userform. The date is auto populated based on todays date and the text box is draws its value from whatever the user enters.

Private Sub btnSubmit_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("InputSheet")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 3) = CDate(Me.tbDate)
ssheet.Cells(nr, 2) = Me.cmblistitem
ssheet.Cells(nr, 1) = Me.tbTicker

My goal here is, depending on what list item is selected I want the name of that item to be pasted in a column that corresponds to that item. i.e if the user selects "apples" and the 3rd column is the "apple" column, I want it to paste in that location.

I am assuming this has to be down with some type of "if" statement.

Any help is appreciated. Here is pic of my worksheet

When you say that the ComboBox "draws its values from a separate dynamically expanding table", do you mean that it's data bound, or are you doing that in code? — Comintern
I used the name manager to tag it and then wrote code: For Each cell In [name] me.cmblistitem.AddItem cell next cell — Anthony
so you'll have "apple" column filled with a bunch of "apples" down its cells? you may want to add some examples of your data structure as well as desired logic — user3598756

user3598756 user3598756 · Accepted Answer · 2016-08-09T17:26:55

supposing I correctly made my guessings, try this code

Option Explicit

Private Sub btnSubmit_Click()
    Dim f As Range

    If Me.cmblistitem.ListIndex = -1 Then Exit Sub '<--| exit if no itemlist has been selected
    If Len(Me.tbTicker) = 0 Then Exit Sub '<--| exit if no item has been input

    With ThisWorkbook.Sheets("InputSheet")
        Set f = .Rows(1).Find(what:=Me.cmblistitem.Value, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False) '<--| look for proper column header
        If f Is Nothing Then Exit Sub '<--| if no header found then exit
        With .Cells(.Cells(Rows.Count, "A").End(xlUp).Row + 1, f.Column) '<--| refer to header column cell corresponding to the first empty one in column "A"
            .Resize(, 3) = Array(Me.tbTicker.Value, Me.cmblistitem.Value, CDate(Me.tbDate)) '<--| write in one shot
        End With
    End With
End Sub

it's commented so you can easily change columns references as per your needs

BTW as for the combobox filling you may want to adopt the following code:

Dim cell As Range
With Me
    For Each cell In [myName]
        .cmblistitem.AddItem cell
    Next cell
End With

which is optimized having referenced Me once before entering the loop so that it's being kept throughout it without further memory accesses

VBA Userform dropdown menu execution

1 Answers