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

Assuming now we have the listbox goto script working lets talk about this script you want to record when it's used into Listbox 2 and three.

Question: Please answer all question

1. Do you run the same macro for each Table
2. Do you have more then one Table on a sheet where you run this script?
For example do you have three tables on the same sheet and you run this same script on all three table.


3. Is this script being run from the Userform?
4. Is the Userform visible while the script runs?

My thinking is if the Userform is not open when Script runs we need to enter "Yes" into Range("A1" on each sheet when the script is run on that sheet. But now if you have more then one Table on the same sheet and you run the same script on different Tables we would have to do something different.

Answer my questions and we will see what we need to do.

 

[My Aswer Is This]

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

I do see you must be working on a big project. I see you have been asking a lot of questions on this forum recently

Are you aware that Mr. Excel provides consulting services at what I would say are reasonable prices.
Check out this link and see if this may be something that could help you speed up getting your entire project working for you:

I do like helping you but I may feel your pain getting a entire project working:

Check out this link:

If you look in the Title bar of this forum you will see this same link:
Mr. Excel consulting

https://www.mrexcel.com/consulting-services/

 

[Waimea]

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

Hi My Aswer Is This,

I am working on multiple small projects and some of the things I ask is common between the projects, like the table manager you are helping me with right now.

I am learning VBA every day and I am getting better and better, I am hoping that I reach the skill level where I, just as you do, can help others!

I have tried commercial services on another forum but nobody accepted my task and the amount I offered was forfeited.

To answer your questions:

1. Yes
2. No, only 1 table per worksheet
3. Yes, from a command button
4. Yes

I think Range("AZ1") would be better because all tables start at A1.

 

[My Aswer Is This]

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

When I look at these few lines of your script I only see this working on One sheet.

With Sheets("report")
With .ListObjects(1)

So do you have a separate script for each sheet. I assumed we were working with more then one sheet with Table.

Or am I wrong?

This script only deals with sheet named "Reports" unless I'm missing something.
​

 

[My Aswer Is This]

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

OK.Lets try this.

You will need to change your initialize code to look like this:
This sets listbox1 listbox2 and listbox3

This assumes you have already loaded Table names into Sheet named "Test" E6 and down using script provided earlier.

Private Sub UserForm_Initialize()
'Modified  12/23/2018  1:03:43 AM  EST
ListBox2.Clear
ListBox3.Clear
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Test").Cells(Rows.Count, "E").End(xlUp).Row
ListBox1.List = Sheets("Test").Range("E6:E" & Lastrow).Value
For i = 1 To Sheets.Count
    If Sheets(i).Range("AZ1").Value = "Yes" Then
        ListBox2.AddItem Sheets(i).Name
    Else
        ListBox3.AddItem Sheets(i).Name
    End If
Next
End Sub

Now put this script in a Command Button on your userform that will run the script you have:

Now my script only popsup a message box that says "Hello"
Change that part of the script to do what you want.

Private Sub CommandButton3_Click()
'Modified  12/23/2018  1:11:52 AM  EST
Dim i As Long
'Put your code below
MsgBox "Hello"

'Your code stops here
'Below part of code needs to stay here
ActiveSheet.Range("AZ1").Value = "Yes"
ListBox2.AddItem ActiveSheet.Name
ListBox3.Clear
For i = 1 To Sheets.Count
If Sheets(i).Range("AZ1").Value <> "Yes" Then ListBox3.AddItem Sheets(i).Name
Next
End Sub

 

[My Aswer Is This]

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

I made a mistake in post 25. Follow those instruction in post 25 but use these two scripts.

Private Sub CommandButton3_Click()
'Modified  12/23/2018  6:23:53 AM  EST
Dim i As Long
'Put your code below
MsgBox "Hello"

'Your code stops here
'Below part of code needs to saty here
ActiveSheet.Range("AZ1").Value = "Yes"
ListBox2.AddItem ActiveSheet.ListObjects(1).Name
ListBox3.Clear
For i = 1 To Sheets.Count
If Sheets(i).Range("AZ1").Value <> "Yes" Then ListBox3.AddItem Sheets(i).ListObjects(1).Name
Next
End Sub
Private Sub UserForm_Initialize()
'Modified  12/23/2018  6:23:53 AM  EST
ListBox2.Clear
ListBox3.Clear
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Test").Cells(Rows.Count, "E").End(xlUp).Row
ListBox1.List = Sheets("Test").Range("E6:E" & Lastrow).Value
For i = 1 To Sheets.Count
    If Sheets(i).Range("AZ1").Value = "Yes" Then
        ListBox2.AddItem Sheets(i).ListObjects(1).Name
    Else
        ListBox3.AddItem Sheets(i).ListObjects(1).Name
    End If
Next
End Sub

 

[Waimea]

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

Hi My Aswer Is This,

I am not at home and I don't have access to Excel at the moment.

I will try your code later today! Thank you for your work with this!

 

[My Aswer Is This]

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

You never got back to me as to if my answer in this post worked.

 

[Waimea]

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

Hi My Aswer Is This,

I am not at home so I don't have access to Excel at the moment.

I'll give you feedback when I have a computer with Excel on it!

Merry Christmas!