Checking certain conditions on a workbook

JJS

Board Regular
Joined
Jul 8, 2009
Messages
81
Hi,

I have a process that currently run weekly which pulls data from 12 individual workbooks into one master workbook for an executive view.

Each user of the individual workbooks has the ability to create a list range in their workbook to add new data using the
Code:
 ActiveSheet.ListObjects.Add(xlSrcRange, Range("list_range"), , xlYes).Name = _
        "List1"
method and an offset named range. This is done to extend data validation fields and formulas to new cells as data is added. I have this set up using a simple macro to start it and a macro to convert it back to a range again.

Problem is that users forget to run the second macro and worksheet stays in list mode making the consolidation process error out when it hits a workbook with this condition.

I am looking for a way to check each workbook when the consolidation macro runs to see if the list mode is still engaged and if so run the convert to range macro. I am unsure if this is possible and what the VBA code would look like for it.

Any help is appreciated, if you need further clarification please let me know
 
Hi JJS,

Here is a function you can use at the beginning of the consolidation process code to determine if the table still exists--if not it has been converted back to a range.

Code:
Function TableExists(TableName As String, Optional SheetName As String = "") As Boolean
   On Error GoTo NoSuchTable
   Dim LO      As ListObject
   Dim WS      As Worksheet
   If SheetName = "" Then
      Set WS = ActiveSheet
   Else
      Set WS = Worksheets(SheetName)
   End If
   Set LO = ActiveSheet.ListObjects(TableName)
   TableExists = True
   Exit Function
NoSuchTable:
   TableExists = False
End Function

Simply place this code in a standard macro module in your workbook. You can then use it in your code to return the table to a simple range like this:

If Not TableExists("List1") Then ActiveSheet.ListObjects("List1").UnList

If the worksheet does not happen to be the active sheet, you can add the optional worksheet name to do it like this:

If Not TableExists("List1","Sheet2") Then Worksheets("Sheet2").ListObjects("List1").UnList

where Sheet2 is the worksheet containing the Table.

Keep Excelling.
 
Upvote 0

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