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

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 !!!

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

Code:

---

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

:thumb

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

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

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