Populating textboxes from combobox with data from another workbook

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
102
Hi, I am needing to populate some textboxes with a combobox from another workbook I can populate the combobox but can not work out how to populate the textboxes using a Sub ComboBox_Change change can anyone help.

Private Sub UserForm_Initialize()
Dim wbExternal As Workbook
Dim wsExternal As Worksheet
Dim lngLastRow As Long
Dim rngExternal As Range
Set wbExternal = Application.Workbooks("PriceSheet.xls")
Set wsExternal = wbExternal.Worksheets("ALL")
lngLastRow = wsExternal.Range("Q" & wsExternal.Rows.Count).End(xlUp).Row
Set rngExternal = wsExternal.Range("A3:A" & CStr(lngLastRow))
ComboBox1.RowSource = rngExternal.Address(External:=True)
End Sub


Regards
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
102
I managed to figure it out :biggrin:

Private Sub UserForm_Initialize()
Dim wbExternal As Workbook
Dim wsExternal As Worksheet
Dim lngLastRow As Long
Dim rngExternal As Range
Set wbExternal = Application.Workbooks("PriceSheet.xls")
Set wsExternal = wbExternal.Worksheets("ALL")
lngLastRow = wsExternal.Range("Q" & wsExternal.Rows.Count).End(xlUp).Row
Set rngExternal = wsExternal.Range("A3:A" & CStr(lngLastRow))
ComboBox1.RowSource = rngExternal.Address(External:=True)
End Sub

Private Sub ComboBox1_Change()
Me.TextBox1.Text = Workbooks("PriceSheet.xls").Worksheets("ALL").Range("B" & Me.ComboBox1.ListIndex + 3).Value
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,287
Messages
5,485,906
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top