Need to remove macro button when creating new sheet

[alleneure]

I have a workbook that has an initial form that I fill out and once I'm done I press a form control button that copies everything to a new sheet and deletes all input information from the initial form except the logo and the form control box. Currently the form control box stays active in the new sheets so I need to delete the form control box before making the new sheet. Current code below work perfectly minus deleting the form control box.

Public Sub CopySheetAndRenameByCell2()
  
Dim wks As Worksheet
  Set wks = ActiveSheet
  ActiveSheet.Copy Before:=Worksheets(Sheets.Count)
  If wks.Range("C3").Value <> "" Then
    On Error Resume Next
    ActiveSheet.Name = wks.Range("C3").Value
  End If
  
  Shapes.Range(Array("Button 1")).Delete 'Used this to try to delete button

  wks.Activate
  
  Sheets("New MRL").Range("c3:c7") = ""
  Sheets("New MRL").Range("c9:d11") = ""
  Sheets("New MRL").Range("c19:c25") = ""
  Sheets("New MRL").Range("h3") = ""
  Sheets("New MRL").Range("d5") = ""
  Sheets("New MRL").Range("c31") = ""
  Sheets("New MRL").Range("h2:h5") = ""
  
   Dim pic As Picture
    
    For Each pic In ActiveSheet.Pictures
        If pic.Name <> "Logo" Then
            pic.Delete
        End If
    Next pic

End Sub

I attempted to delete button with the line shown but as I'm not super familiar with VBA I'm sure I'm doing something wrong. Thanks

 

[rlv01]

Let's say the form button on worksheet wks shows text that says "Run Macro #1". Here is one way to delete that button

    Dim B As Button
    Dim wks As Worksheet
    Set wks = ActiveSheet

        For Each B In wks.Buttons
            If B.Caption = "Run Macro #1" Then
                B.Delete  'Delete the button labeled "Run Macro #1"
            End If
        Next B

 

[alleneure]

Let's say the form button on worksheet wks shows text that says "Run Macro #1". Here is one way to delete that button

    Dim B As Button
    Dim wks As Worksheet
    Set wks = ActiveSheet

        For Each B In wks.Buttons
            If B.Caption = "Run Macro #1" Then
                B.Delete  'Delete the button labeled "Run Macro #1"
            End If
        Next B

Thanks! That worked for deleting the button but it deleted the active sheet button and not the new sheet button. The new sheet has the button remaining and blank form has the button deleted button Modifications?

 

[rlv01]

You could try

Dim wks As Worksheet
  Set wks = ActiveSheet
  ActiveSheet.Copy Before:=Worksheets(Sheets.Count)
  If wks.Range("C3").Value <> "" Then
    On Error Resume Next
    ActiveSheet.Name = wks.Range("C3").Value
  End If

    Dim B As Button
        For Each B In ActiveSheet.Buttons
            If B.Caption = "Run Macro #1" Then
                B.Delete  'Delete the button labeled "Run Macro #1"
            End If
        Next B

 

[alleneure]

I changed my mind and what to name new sheet after cells C3 and C5. I tried the below code and it did not work properly. Any suggestions?

Public Sub CopySheetAndRenameByCell2()
  
Dim wks As Worksheet
  Set wks = ActiveSheet
  ActiveSheet.Copy Before:=Worksheets(Sheets.Count)
  If wks.Range("C3","C5").Value <> "" Then
    On Error Resume Next
    ActiveSheet.Name = wks.Range("C3","C5").Value
  End If

 

[rlv01]

wks.Range("C3","C5").Value references multiple cells. You can't use that to name your worksheet. It needs to be just a single cell.

 

[alleneure]

wks.Range("C3","C5").Value references multiple cells. You can't use that to name your worksheet. It needs to be just a single cell.

Thanks

 

[daverunt]

You can use a variable to name the sheet with those references.
With the current code if c3 or c5 is empty then your variable is also empty and Excel will name the sheet as a copy with a (2)
You'd need to decide what you want to name the sheet if either of those cells is empty.

  If wks.Range("C3").Value <> "" And wks.Range("C5").Value <> "" Then
  NewSheetName = wks.Range("C3").Value & " " & wks.Range("C5").Value
  ActiveSheet.Name = NewSheetName
  Else
  ActiveSheet.Name = "c3 or 5 is empty"
  End If