keithaul
09-16-2016, 09:40 AM
I know there are various ways to populate a listbox in an excel 2016 userform with data from a specific sheet in my workbook. However his is my problem
If I have the following code in my initialize event of the user form, I want to display the data from the specific sheet I specify in my VBA code, not the data from the currently selected sheet in my workbook. Whenever I have a different sheet selected in my workbook, other than the sheet named in the VBA code, it always pulls the data from the current selected sheet. the following code only works when the sheet named in the code equals the current selected sheet in the workbook
so how can i change the following code so it always pulls data from the sheet named in the code, regardless of what sheet is selected in the workbook?
Dim rSource As Range
Dim LastRow As Integer, LastCol As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sold Items ListObjects")
'If the source range is in a table named "SoldItemsListObj" on Sheet "Sold Items ListObjects":
' ListObjects - represents a list of tables in the worksheet
' DataBodyRange - represents the table data, excluding the header
' List Property of a Listbox holds the items/list in the list box
lbSoldItems3.List = Worksheets("Sold Items ListObjects").ListObjects("SoldItemsListObj").DataBodyRange.Value
'Lets get the last row that has data in it.
' start at last row in the worksheet, 1st column;
' press the end key + the up arrow key will bring you to the last row filled with data
LastRow = Worksheets("Sold Items ListObjects").Cells(Rows.Count, 1).End(xlUp).Row
'Lets get the last column that has data in it.
' start at row 1, last column in worksheet;
' press the end key + the left arrow key will bring you to the last column with data in it
LastCol = Worksheets("Sold Items ListObjects").Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets("Sold Items ListObjects")
'set the range to - start in row 2, column one and end at last row with data and last column with data
Set rSource = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With
With frmListObjects.lbSoldItems3
.RowSource = rSource.Address 'return the address range of the data you want to display in the listbox
.ColumnHeads = True 'display the column headings
.ColumnCount = .ColumnHeads '# of columns you want displayed
End With
If I have the following code in my initialize event of the user form, I want to display the data from the specific sheet I specify in my VBA code, not the data from the currently selected sheet in my workbook. Whenever I have a different sheet selected in my workbook, other than the sheet named in the VBA code, it always pulls the data from the current selected sheet. the following code only works when the sheet named in the code equals the current selected sheet in the workbook
so how can i change the following code so it always pulls data from the sheet named in the code, regardless of what sheet is selected in the workbook?
Dim rSource As Range
Dim LastRow As Integer, LastCol As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sold Items ListObjects")
'If the source range is in a table named "SoldItemsListObj" on Sheet "Sold Items ListObjects":
' ListObjects - represents a list of tables in the worksheet
' DataBodyRange - represents the table data, excluding the header
' List Property of a Listbox holds the items/list in the list box
lbSoldItems3.List = Worksheets("Sold Items ListObjects").ListObjects("SoldItemsListObj").DataBodyRange.Value
'Lets get the last row that has data in it.
' start at last row in the worksheet, 1st column;
' press the end key + the up arrow key will bring you to the last row filled with data
LastRow = Worksheets("Sold Items ListObjects").Cells(Rows.Count, 1).End(xlUp).Row
'Lets get the last column that has data in it.
' start at row 1, last column in worksheet;
' press the end key + the left arrow key will bring you to the last column with data in it
LastCol = Worksheets("Sold Items ListObjects").Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets("Sold Items ListObjects")
'set the range to - start in row 2, column one and end at last row with data and last column with data
Set rSource = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With
With frmListObjects.lbSoldItems3
.RowSource = rSource.Address 'return the address range of the data you want to display in the listbox
.ColumnHeads = True 'display the column headings
.ColumnCount = .ColumnHeads '# of columns you want displayed
End With