Command Button Confusion

[teachman]

Hello,

I want a command button on a worksheet that calls an error checking macro. Since I use this error check macro on more than one worksheet I put the code in a module, rather than duplicate the code in each worksheet.

I'm using Excel 2007 and there are two ways to create the command button. Form Controls and ActiveX Form Controls. I'd like to spruce up the button, at least with a color different than gray. So, I used the ActiveX form control Command Button. I did spruce it up a bit, but then I can't link it to my error checking macro, which I put in a module. If I use an old fashioned Form Control Command Button, I can link it to the macro in the module but I can't spruce the button up.

Normally, I'm a function over form kind of guy, but in this case I'd like a little more form with the same function.

The question is how can I link the macro code in a module to an ActiveX Command Button?

Thanks for your help.

George Teachman

 

[VoG]

Something like this

Private Sub CommandButton1_Click()
Call mymacro
End Sub

 

[teachman]

VoG or Peter (not sure which)

I tried that and I was rewarded with 'Compile error; Expected procedure, not variable.'

I was using:

Sub ErrorCheck_Click()
Call ErrorCheck
End Sub

That's when I noticed that I was calling the ErrorCheck macro in a module from a macro called ErrorCheck_Click. I got to wondering if I was confusing VBA. Lord knows, it's confused me often enough. Anyway, I renamed my macro in the module to ErrorCheck2 (I'm nothing if not persistent) and changed the above code to:

Sub ErrorCheck_Click()
Call ErrorCheck2
End Sub

Now it works.

Thanks for your help.

George

Something like this

Private Sub CommandButton1_Click()
Call mymacro
End Sub