vba to delete buttons in a worksheet with criteria

[cjcass]

Hi,
I have the code below that deletes all buttons throughout a workbook.
I would like to be able to adapt the code to do the following:
(a) delete all buttons in the workbook except any buttons found in the worksheet named "Export Tabs"
(b) delete all buttons in the workbook except any buttons found that are named "Export1" and 'Export2" in any worksheet
These are independent solutions, so would like two separate codes if possible.
Hope you can help, many thanks.

For Each b In Worksheets
        
    b.Buttons.Delete
    
    Next b

 

[Engberg]

Try this.

Separated:

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Name <> "Export Tabs" Then
            btn.Delete
            End If
        Next
    Next

End Sub

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Buttons(btn.Name).Name <> "Export1" And sht.Buttons(btn.Name).Name <> "Export2" Then
            btn.Delete
            End If
        Next
    Next

End Sub

Combined:

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Name <> "Export Tabs" And sht.Buttons(btn.Name).Name <> "Export1" And sht.Buttons(btn.Name).Name <> "Export2" Then
            btn.Delete
            End If
        Next
    Next

End Sub

 

[dmt32]

These are independent solutions, so would like two separate codes if possible.

Should only need one code to perform required operation

Place in standard module

Sub DeleteAllButtonsExcept(ParamArray KeepButtons() As Variant)
    Dim sh   As Worksheet
    Dim shp  As Shape

    For Each sh In ThisWorkbook.Worksheets
        For Each shp In sh.Shapes
           m = Application.Match(shp.Name, Array(KeepButtons), 0)
           If IsError(m) Then shp.Delete
        Next shp
    Next sh
   
End Sub

Then from your code, call the sub passing to it the button name(s) you want to keep

    DeleteAllButtonsExcept "Export Tabs"
   
OR

    DeleteAllButtonsExcept "Export1", "Export2"

OR
   
    DeleteAllButtonsExcept "Export Tabs", "Export1", "Export2"

The buttons names argument list can be as many as required
Solution not fully tested but hopefully, will do what you want

Hope Helpful

Dave

 

[cjcass]

Try this.

Separated:

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Name <> "Export Tabs" Then
            btn.Delete
            End If
        Next
    Next

End Sub

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Buttons(btn.Name).Name <> "Export1" And sht.Buttons(btn.Name).Name <> "Export2" Then
            btn.Delete
            End If
        Next
    Next

End Sub

Combined:

Sub deleteButtonsConditional()

    For Each sht In Worksheets
        For Each btn In sht.Buttons
            If sht.Name <> "Export Tabs" And sht.Buttons(btn.Name).Name <> "Export1" And sht.Buttons(btn.Name).Name <> "Export2" Then
            btn.Delete
            End If
        Next
    Next

End Sub

they work perfectly, thank you very much for your time and help with this

 

[cjcass]

Should only need one code to perform required operation

Place in standard module

Sub DeleteAllButtonsExcept(ParamArray KeepButtons() As Variant)
    Dim sh   As Worksheet
    Dim shp  As Shape

    For Each sh In ThisWorkbook.Worksheets
        For Each shp In sh.Shapes
           m = Application.Match(shp.Name, Array(KeepButtons), 0)
           If IsError(m) Then shp.Delete
        Next shp
    Next sh
  
End Sub

Then from your code, call the sub passing to it the button name(s) you want to keep

    DeleteAllButtonsExcept "Export Tabs"
  
OR

    DeleteAllButtonsExcept "Export1", "Export2"

OR
  
    DeleteAllButtonsExcept "Export Tabs", "Export1", "Export2"

The buttons names argument list can be as many as required
Solution not fully tested but hopefully, will do what you want

Hope Helpful

Dave

ok thanks for this I will try this solution also