Forum Discussion

oteixeira62's avatar
oteixeira62
Copper Contributor
Jan 14, 2023
Solved

Store select item from listbox to textbox

I have this UserForm with a listbox of items stored in Sheet, column A. Whenever I select a country from the listbox, the country name is displayed in the textbox bellow the countries list.

My goal is to put the ISO code of the country inside the textbox, instead of the country name.

Can someone please give a hand on this?

 

Here's the code I'm using:

 

Option Explicit
Private Sub ListBox1_Change()
    Dim k As Integer
    For k = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(k) Then TextBox2.Value = ListBox1.Text
        'TextBox2.Value = Me.ListBox1.List(k)
    Next k
End Sub
Private Sub UserForm_Initialize()
    Label1.Caption = Sheets("Sheet1").Range("A1").Value
    ListBox1.List = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).Value
End Sub

 

Many thanks for any kind help.

Octavio

  • oteixeira62 

    Set the ColumnCount property of the list box to 2.

    In the UserForm_Initialize procedure, change

        ListBox1.List = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).Value

    to

        ListBox1.List = Sheets("Sheet1").Range("A2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Value
    

     In the ListBox1_Change procedure, change

        If ListBox1.Selected(k) Then TextBox2.Value = ListBox1.Text

    to

        If ListBox1.Selected(k) Then TextBox2.Value = ListBox1.Column(1)

2 Replies

  • oteixeira62 

    Set the ColumnCount property of the list box to 2.

    In the UserForm_Initialize procedure, change

        ListBox1.List = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).Value

    to

        ListBox1.List = Sheets("Sheet1").Range("A2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Value
    

     In the ListBox1_Change procedure, change

        If ListBox1.Selected(k) Then TextBox2.Value = ListBox1.Text

    to

        If ListBox1.Selected(k) Then TextBox2.Value = ListBox1.Column(1)
    • oteixeira62's avatar
      oteixeira62
      Copper Contributor
      Thank You very much Hans!
      That works perfectly.
      Octavio

Resources