Create a list of all tables in a workbook

[Waimea]

Hi,

I am trying to create a list of all tables in a workbook and I am also trying to create an userform with one or more list boxes with the table names in them.

The listbox is called listbox1, listbox2 and listbox3.

Sub tableAllSheet()

    Dim sh As Worksheet
    Dim tbl As ListObject


    'Loop through all sheets
    For Each sh In ThisWorkbook.Worksheets
        'Loop through all table on a sheet

        For Each tbl In sh.ListObjects
            'Print table name, table header row address and data range address to Immediate window
            Debug.Print tbl.Name & vbTab & tbl.HeaderRowRange.Address & vbTab & tbl.DataBodyRange.Address
        Next tbl
    Next sh


End Sub

This code debug prints the information I am looking for and I want to print it to a worksheet?

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

So is it correct you did not write this code.
You found it someplace but it does not do what you want.

So you want all the Table names entered where?

And you want the same Table names entered into three listbox's on your userform.

Why do you need three listboxs with the same table names?

 

[Waimea]

Re: How to create a list of all tables in a workbook

Hi My Aswer Is This,

I did not write this code and I want to modify it. I want to modify it to print out the information to a worksheet "Test" starting from cell E6.

I did think about the code you wrote for me that show or hides sheets using listboxes, perhaps that code could be modified?

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

I did not notice your name as someone I had helped before. Sorry

So Ok you want all Table names entered into a sheet named "Test"
Starting in Range("E6")

Try this:

Sub tableAllSheet()
'Modified  12/21/2018  3:05:27 AM  EST
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim tbl As ListObject
Dim x As Long
x = 6
    
    For Each sh In ThisWorkbook.Worksheets
        For Each tbl In sh.ListObjects
            Sheets("Test").Cells(x, 5).Value = tbl.Name
            x = x + 1
        Next tbl
    Next sh
Application.ScreenUpdating = True
End Sub

Now tell me more about adding names to userform Listboxes.

 

[Waimea]

Re: How to create a list of all tables in a workbook

Thank you for your reply, it works great.

I am trying to create a table manager that in one listbox, listbox1, shows all table names, if I click on the table name I Application.Goto that table.

I another listbox, listbox2, I would like to show tables where I haven't run a specific macro. The macro creates a summary of the table.

In a third listbox I would like to show tables where the summary macro has been run.

Perhaps using a flag (TRUE or FALSE) to keep track on which tables the macro has been used?

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Now after you load all your table names into sheet Test.
You can put this script into your userform
For loading into Listbox 1 2 and 3.

Private Sub UserForm_Initialize()
'Modified  12/21/2018  3:15:51 AM  EST
Dim Lastrow As Long
Lastrow = Sheets("Test").Cells(Rows.Count, "E").End(xlUp).Row
ListBox1.List = Sheets("Test").Range("E6:E" & Lastrow).Value
ListBox2.List = Sheets("Test").Range("E6:E" & Lastrow).Value
ListBox3.List = Sheets("Test").Range("E6:E" & Lastrow).Value
End Sub

 

[Waimea]

Re: How to create a list of all tables in a workbook

Thank you for your reply My Aswer Is This.

Your code loads all the table names into all 3 listboxes!

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Thank you for your reply My Aswer Is This.

Your code loads all the table names into all 3 listboxes!

I believe that is what you asked for in post 1:
Here is what you asked for:

I am trying to create a list of all tables in a workbook and I am also trying to create an userform with one or more list boxes with the table names in them.

The listbox is called listbox1, listbox2 and listbox3.​

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Getting back to Goto Table and record when script is run may take me some time to think about.

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Use this script as a way to Goto Table

When you click on a Table name in listbox1 you will be taken to that table.

Private Sub ListBox1_Click()
'Modified  12/21/2018  4:44:09 AM  EST
Dim ans As String
ans = ListBox1.Value
Dim pws As Worksheet, sws As String
sws = Range(ans).Parent.Name
Application.Goto Sheets(sws).Range(ans)
End Sub