Deselect Single Item (Button) from Multiple Selection

[KingB69]

I have a worksheet which a user can populate with text, pictures and shapes. To prevent the user from causing issues with the worksheet, it is protected, and there is a Command Button which the user can click to insert another worksheet which is an identical copy of the original.

I have been able to delete all text and format relevant cells etc., but when I try to delete the other items, pictures and shapes using an ActiveSheet.Shapes.SelectAll command, I need to "deselect" the Command Button "Button 1" from the selection.

I have tried to change button visibility to false and then deleting the shapes, which generally works. However, if I try the delete command on a sheet without any shapes, the button does not reappear. It still exists within the worksheet as I can go into the VBA Editor and change its visibilty in the Immediate window, but I do not know why the code only works once, as in the button never successfully re-appears more than once?

 

[Peter_SSs]

Welcome to the MrExcel board!

Would something like this be any use? (Test with a copy of your workbook)

Sub Test()
  Dim sh As Shape
  Dim ShapesToSelect As String
  
  For Each sh In ActiveSheet.Shapes
    If sh.Name <> "Button1" Then ShapesToSelect = ShapesToSelect & "," & sh.Name
  Next sh
  If Len(ShapesToSelect) > 0 Then ActiveSheet.Shapes.Range(Split(Mid(ShapesToSelect, 2), ",")).Delete
End Sub

 

[KingB69]

For Each sh In ActiveSheet.Shapes If sh.Name <> "Button1" Then ShapesToSelect = ShapesToSelect & "," & sh.Name Next sh If Len(ShapesToSelect) > 0 Then ActiveSheet.Shapes.Range(Split(Mid(ShapesToSelect, 2), ",")).Delete

Dear Peter_SSs, after a minor change (it was Button 1) it works perfectly!

Many thanks!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.