View Full Version : [SOLVED:] Office 365 - Remove All Tables, ListBox & Macros from a Dynamic Range of Worksheets
JILBO
05-25-2021, 12:25 PM
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
05-30-2021, 10:48 AM
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
JILBO
05-31-2021, 12:31 AM
Thanks for looking at this ! I’m off to try it !!
JILBO
06-15-2021, 02:26 AM
Hi, Im getting a 'Compile Error: Sub or Function not defined" i've tried and failed miserably at trying to understand why??
JILBO
06-15-2021, 05:31 AM
Figured it out!...was a 'Space' when deleted it works perfectly. Many Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.