CODEWITHSUNDEEP
vbaexceltextboxlistbox
Alec
Alec

Reputation: 141

VBA: TextBox and ListBox

On my UserForm I have a TextBox where is write down my Serial Numbers, something like 421012. Below it I have a ListBox with options like "Tools, Bench, Wheel"

What I'm attemting to do:

After I type my Serial that I just typed in, I click on one of the options in the ListBox and it should put a value behind my Serial.

Something like this: 421012 + "selecting Tools" =42101215

the "Bench" would get an result of 42101245

My code so far:

Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Tools"
.AddItem "Bench"
.AddItem "Wheel"
End With
End Sub

Somehow I have to tell VBA that Tools equals 15 or Bench equals 45 etc.etc.

Upvotes: 0

Views: 1124

Answers (2)

Comintern
Comintern

Reputation: 22205

I use this much more frequently with objects than with intrinsic types, but another option is to store a Dictionary containing lookups keyed on your ListBox items:

'Module level variable
Private SerialLookup As Scripting.Dictionary

Private Sub UserForm_Initialize()
    Set SerialLookup = New Scripting.Dictionary
    With SerialLookup
        .Add "Tools", "15"
        .Add "Bench", "45"
        .Add "Wheel", "42"
    End With

    ListBox1.List = SerialLookup.Keys()
End Sub

Then you can retrieve whatever value you need by using it as an index into the Dictionary, i.e.:

TextBox1.Value = "421012" & SerialLookup.Item(ListBox1.Value)

Upvotes: 1

geeFlo
geeFlo

Reputation: 375

Add a second column and then reference that when you're doing your concatenation of the value. "42012" & me.listbox.column(1) should return the value of whatever is selected

with listbox
    .columncount = 2
    .additem
    .list(1,0) = "Tools"
    .list(1,1) = 15
    .list(2,0) = "Bench"
    .list(2,1) = 45
    .list(3,0) = "Wheel"
    .list(3,1) = 75
end with

Upvotes: 1

Related Questions