Office 365 - Remove All Tables, ListBox & Macros from a Dynamic Range of Worksheets

[JILBO]

Hi All,


I'm looking for amethod of Removing all Tables (Table Names Start with Step_BOM), List Objects(ActiveX List called ListBox1) and VBA/Macros from the Sheet. The Sheets I wantto implement this on are always start with 'Step' and there could be upto 50 ofthem so would need to loop until complete.


I've attached anexample

Many Thanks Onceagain !!!

[Artik]

The following code removes Tables, ComboBoxes, and ListBoxes from worksheets named "Step # *".

Sub RemoveTablesCombosAndLists()
    Dim Sh          As Worksheet
    Dim Tbl         As ListObject
    Dim objOLE      As OLEObject

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each Sh In ThisWorkbook.Worksheets

        If LCase(Sh.Name) Like "step#*" Then

            For Each Tbl In Sh.ListObjects
                If LCase(Tbl.Name) Like "step_bom#*" Then
                    'remove Table
                    Tbl.Range.Clear
                End If
            Next Tbl

            For Each objOLE In Sh.OLEObjects
                'remove ListBoxes AND ComboBoxes from sheet
                If objOLE.progID = "Forms.ListBox.1" Or objOLE.progID = "Forms.ComboBox.1" Then
                    objOLE.Delete
                End If
            Next objOLE

        End If

    Next Sh
    
    Application.EnableEvents = True
End Sub

You have the procedure for removing code from sheet modules in another thread.

Artik

[SamT]

[JILBO]

Thanks for looking at this ! I’m off to try it !!

[JILBO]

Hi, Im getting a 'Compile Error: Sub or Function not defined" i've tried and failed miserably at trying to understand why??

[JILBO]

Figured it out!...was a 'Space' when deleted it works perfectly. Many Thanks