Private vs not in sub function using Buttons

Hello,

I have 2 sheets. One "monthly data" has all of my formulas. The 2nd one, "Data from ECS" has the actual data. So I went from a regular button associated with a Macro:

Sub ClearPatientData()
'
' ClearPatientData
'

'
 Sheets("Data from ECS").Select
    Columns("J:K").Select
    Selection.Clear
Range("A1").Select

    
End Sub

To an ActiveX button so that I could change it's color/formatting. I wasn't able to figure out how to link the new button to the macro so I just copied the code into the function that was created:

Private Sub CommandButton1_Click()
'
' ClearPatientData
'

'
 Sheets("Data from ECS").Select
    Columns("J:K").Select
    Selection.Clear
Range("A1").Select
End Sub

They both are supposed to do the same thing. The original function is located in the sheet called "Data from ECS", however the newer code is located in the sheet where the button is located.

When I click the button, I get an error: Run-time error '1004': Select method of Range class failed.

What am I doing wrong?

TIA

You want:

Private Sub CommandButton1_Click()
    Columns("J:K").Clear
    Range("A1").Select
End Sub

But you should really use a shape rather than an ActiveX button on a sheet:

You're better off not using ActiveX controls on worksheets at all. They're incredibly unstable have a look here https://www.google.co.uk/webhp?sourc...eet%20controls and by using them, you're asking for trouble.

Forms controls are much more reliable - they're in the same dropdown as the ActiveX ones, but are designed for use on worksheets - so you don't run into any stability issues, you can do most of the same things with them too and you can use them without using code.

I used the button on the Form controls group, but I can't figure out how to change the color of the button. That's why I went to the ActiveX one.

Also, the button is on the sheet with the formulas, not the one with the data. I want to make sure that those columns are cleared on the data sheet and not the main one with the formulas. I think the edit you gave me above would clear the columns on the wrong sheet.