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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
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,448
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,448
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,529
Members
410,845
Latest member
OldSwimmer1650
Top