Steps to do:
- Add new shee and change its name to "
Data
" - Lets
A1:B1
range will be the headers: Item
and Price
respectively - Add sample data
- Go to VBA code editor (ALT+F11)
- Add module (Tools->Insert->Module)
- Copy and paste below code
Option Explicit
Sub Load2Columns(ByRef oLst As MSForms.ListBox, ByRef wsh As Worksheet, Optional ByVal sFirstColumn As String = "A", Optional ByVal iFirstRow As Integer = 2)
Dim i As Integer
With oLst
Do While wsh.Range(sFirstColumn & iFirstRow) <> ""
.AddItem ""
i = .ListCount - 1
.Column(0, i) = wsh.Range(sFirstColumn & iFirstRow)
.Column(1, i) = wsh.Range(sFirstColumn & iFirstRow).Offset(ColumnOffset:=1)
iFirstRow = iFirstRow + 1
Loop
End With
End Sub
- Add UserForm (Tools->Insert->UserForm
- From ToolBox add
<il>
- Copy and paste below code:
Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()
Me.TextBox1 = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
End Sub
Private Sub UserForm_Initialize()
Load2Columns Me.ListBox1, ThisWorkbook.Worksheets("Data")
With Me.ListBox1
.ColumnCount = 2
.ColumnWidths = ";0"
.ListIndex = 0
End With
End Sub
- That's all! Now, when you change selection for ListBox, the price will be loadded to the TextBox
Here[^] you can download an example.