ActiveX button is appearing on all worksheets

Question

I'm new to VBA and I'm unable to find answer to this. I have ActiveX Button on one sheet. I'm also creating new sheets is same workbook. Problem is that button is appearing on all of those worksheets. I would like that button to appear only on one worksheet, so it will not be on other worksheets that I create with VBA.

Code in separate module:

Option Explicit

Sub copying()

Worksheets(1).Copy After:=Worksheets(Worksheets.Count)

Worksheets(2).Activate

Worksheets(2).Name = "Test"

End Sub

Code for button that is calling copying function:

Option Explicit

Private Sub CommandButton1_Click()

Call copying

End Sub

Button is appearing on new worksheet that is created.

Thank you.

Did you check line 37 in your code? I'd check it for you, but you didn't post it. :( Tough to help debug without seeing the code. :) — Tim, Aug 07 '16 at 17:28

score 3 · Accepted Answer · answered Aug 07 '16 at 18:28

You're using the Worksheet.Copy method which copies the sheet and all of its contents including your button. Either use the Sheets.Add method(https://msdn.microsoft.com/en-us/library/office/ff839847.aspx) or copy the sheet like you already do and delete the button.

.Add will just add a new sheet:

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

.Copy will copy the contents of the sheet. If you have a lot of data on the sheet you want copied, it will be easier to copy the sheet and delete the extra button:

Option Explicit

Sub copying()
    Worksheets(1).Copy After:=Worksheets(Worksheets.Count)
    Worksheets(2).Activate
    Worksheets(2).Name = "Test"

    Dim objShape As Shape
    For Each objShape In ActiveSheet.Shapes 
        ' I think 12 is the msoShapeType you need.
        If objShape.Type = 12 Then objShape.Delete
    Next objShape
End Sub

Good solution, but I'd use the constant `msoOLEControlObject` instead of `12`. — Comintern, Aug 07 '16 at 18:37

ActiveX button is appearing on all worksheets

1 Answers1