fill listbox, from combobox selection, with closed workbook data

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
Hi everyone
with this code, in the combobox, I get the list of sheets in the 'orders' workbook.
I would like that when I select an item, the listbox would be populated with the data present in the A4: J110 range of the sheet, with the name of the selected item.
VBA Code:
Private Sub UserForm_Initialize()
Dim i As Long
Dim Databook As Workbook

Application.ScreenUpdating = False
Set Databook = Workbooks.Open(ThisWorkbook.Path & "/" & "ordini.xlsm")
For i = 1 To Sheets("Foglio1").Range("A100").End(xlUp).Offset(1, 2).Row
   Me.ComboBox1.AddItem Sheets("Foglio1").Cells(i, 2)
Next i

Databook.Save
Databook.Close
Application.ScreenUpdating = True

End Sub

Private Sub ComboBox1_Change()
txtbox1.Value = ComboBox1.Value
End Sub

Private Sub txtbox1_Change() 'textbox whose value initializes the listbox

End Sub

I tried assigning a variable to the combobox value to load the data but it tells me that the source is not valid
Please can you explain to me how I can do it
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,184
Office Version
  1. 365
Platform
  1. Windows
How about
Rich (BB code):
Dim Databook As Workbook

Private Sub UserForm_Initialize()
Dim i As Long

Application.ScreenUpdating = False
Set Databook = Workbooks.Open(ThisWorkbook.Path & "/" & "ordini.xlsm")
For i = 1 To Sheets("Foglio1").Range("A100").End(xlUp).Offset(1, 2).Row
   Me.ComboBox1.AddItem Sheets("Foglio1").Cells(i, 2)
Next i

Application.ScreenUpdating = True

End Sub

Private Sub ComboBox1_Click()
   Me.ListBox1.List = Databook.Sheets(Me.ComboBox1.Value).Range("A4:J110").Value
End Sub
The line in blue must be at the very top of the module, before any code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,184
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,179
Members
410,731
Latest member
keobongmacao
Top