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]

:thumb

[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