CODEWITHSUNDEEP
excelvbacomboboxvlookup
squar_o
squar_o

Reputation: 567

Set cell vlookup value based on changing Combobox value

I have input a combobox in an Excel sheet. I want it to work so that the user who does not have access to the VBA can select a value from the dropdown and then the value in another cell will perform a vlookup on this value.

In the first instance I have inserted a box and am trying to set a cell value based on this. 

Sub InsertComboBox()

#inserts dropdown box on the front page and sets the values as the list of DMA from the pipe_totals sheet
#this should be the most complete list so will not change dependant on asset

Dim arearange As Range
Set arearange = Sheets("pipe_totals").Range("a:a")
lastrowi = Application.WorksheetFunction.CountA(arearange)

Sheets("front page").Activate
With Range("f5:g5")
Set Combo = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)

End With

Combo.List() = Sheets("pipe_totals").Range("A2:A" & lastrowi).Value
.Range("k9").Value = Combo.Value 'only works on current combobox value which is 0

End Sub

Is there a way I can set this so the vlookup is dynamic depending on the users selection?

Upvotes: 1

Views: 5308

Answers (2)

mits
mits

Reputation: 926

In this example, just set the right combo name. It should be ok, provided that your combobox lists values from "Range("A2:A" & lastrowi)" as you mention above.

Sub "comboname"_Change()
    Dim list_val As Long

    list_val = Worksheets("front page").Shapes("comboname").ControlFormat.Value
    Range("K9") = Worksheets("pipe_totals").Cells((list_val + 1), 1)
End Sub

Sub test()
    Dim z As Shape
        For Each z In Worksheets("front page").Shapes
            Debug.Print z.Name
        Next z
End Sub

Upvotes: 2

mits
mits

Reputation: 926

As far as I understand, you want that everytime the combobox value changes, cell K9 will have the same value also. Is that right? If this the case, then right click on the combobox and select "Assign Macro". Then select "Create". Then inside the sub created, which should look like this:

Sub "comboname"_Change()

End Sub

You should also paste the final code line.

.Range("k9").Value = Combo.Value

Doing so, means you want that line of code executed every time the combobox value changes.

Upvotes: 0

Related Questions