Private vs not in sub function using Buttons

[cajunlibra]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

[Kyle123]

You want:

Please Login or Register  to view this content.

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.

[cajunlibra]

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.

[shg]

... but I can't figure out how to change the color of the button.

You can use an autoshape and make it any color you like.