Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Dec 2020
    Posts
    38
    Location

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

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    385
    Location
    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

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Regular
    Joined
    Dec 2020
    Posts
    38
    Location
    Thanks for looking at this ! I’m off to try it !!

  5. #5
    VBAX Regular
    Joined
    Dec 2020
    Posts
    38
    Location
    Hi, Im getting a 'Compile Error: Sub or Function not defined" i've tried and failed miserably at trying to understand why??

  6. #6
    VBAX Regular
    Joined
    Dec 2020
    Posts
    38
    Location
    Figured it out!...was a 'Space' when deleted it works perfectly. Many Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •