Data from Table to Listbox

arnabmit

New Member
Joined
Mar 28, 2009
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

I am trying to get the content of a formatted table into an ActiveX list box lstPrev. I am getting error 438 "Object doesn't support this property or method".

What am I doing wrong?

Note: ShtName is a public variable

VBA Code:
Dim dbSheet As Worksheet
Dim dbTable As ListObject

Set dbSheet = ThisWorkbook.Sheets("Form")
Set dbTable = ThisWorkbook.Sheets(ShtName).ListObjects(ShtName)

ActiveSheet.lstPrev.RowSource = dbTable.DataBodyRange.Address(External:=True)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
ActiveX listboxes do not have a rowsource property, try
VBA Code:
ActiveSheet.lstPrev.List = dbTable.DataBodyRange.Value
 

arnabmit

New Member
Joined
Mar 28, 2009
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
ActiveX listboxes do not have a rowsource property, try
VBA Code:
ActiveSheet.lstPrev.List = dbTable.DataBodyRange.Value
Thank you! Perils of being a noob.

I am guessing the following property is also not available? because I see the column header, but without any text

VBA Code:
ActiveSheet.lstPrev.ColumnHeads = True
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
If you want the header use ListFillRange instead
VBA Code:
ActiveSheet.LstPrev.ListFillRange = dbTable.DataBodyRange.Address(, , , 1)
 
Solution

arnabmit

New Member
Joined
Mar 28, 2009
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you want the header use ListFillRange instead
VBA Code:
ActiveSheet.LstPrev.ListFillRange = dbTable.DataBodyRange.Address(, , , 1)

Worked perfectly! Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,590
Messages
5,548,897
Members
410,884
Latest member
schreiberralph
Top