Creating a ListBox on a worksheet

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
161
Hi all,

I am struggling to get a listbox working. I have created it on a worksheet and wish for it tp be populated by data from a table on another worksheet within the same workbook. I have tried using the below code but it doesn't seem to work. I'm not sure if i'f i'm putting it in the wrong place or what i'm doing wrong. Any help is always greatly appreciated.

Stay safe all

VBA Code:
    Dim tbl As ListObject
    Dim cell As Range

'Store Table Object to a variable
    Set tbl = Sheet6.ListObjects("Table1")
 
'Load List Box
  For Each cell In tbl.DataBodyRange.Columns(6).Cells
    ListBox1.AddItem cell.Value
  Next cell
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Are you using an ActiveX listbox & if so is that code located in the sheet module, that contains the listbox?
 
Upvote 0
Yes I'm using an ActiveX Listbox and it is within the worksheet that the listbox is on. On Worksheet_Activate.
 
Upvote 0
In that case it should work when you move to that sheet, do you get any error messages?
 
Upvote 0
No I don't get any error message. What is actually happening now is everytime I go to the worksheet with the listbox on it adds the same data from the table, but only the last column.

The table where the data comes from has 6 columns and headers. I have the listbox showing 6 columns and headers but they aren't filled. At the moment I have 3 rows of data in my table plus headers. Each time I click on the worksheet with the listbox it adds 3 figures to the first column of the listbox so if i select another worksheet and then go back it goes from having 3 bits of data to having 6 even though there is only 3 in the table.

In essence I want the listbox to replicate the table
 
Upvote 0
In that case, how about
VBA Code:
Private Sub Worksheet_Activate()
    Dim tbl As ListObject

'Store Table Object to a variable
    Set tbl = Sheet6.ListObjects("Table1")
 
'Load List Box
    ListBox1.List = tbl.DataBodyRange.Value

End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Column 2 within that listbox contains worksheet names. Is there a simple way that when I select a row in the listbox it takes me to the worksheet?
 
Upvote 0
You can double click the listbox & use
VBA Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.ListBox1
      Sheets(.List(.ListIndex, 1)).Activate
   End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top