Convert a Shape to a Command Button

[michaelch2934]

I'm sure this is an age old problem. The difference in Command Buttons and Shapes. And while you can run Macros by clicking a Shape, you can't run VBA Code by clicking on a Shape. Am I right?

I've written workable VBA code for Command Buttons. They work fine. But, the look and shape of some of the Command Buttons (Rectangular) will not work for my application. So, I created these nifty Shapes to look just like I want (Arrows).

Now everyone tells me all I have to do is assign a Macro to the shape and be happy. But, unless I can make a Macro run my VBA code, this will not work. (The code is very complex and has many If Then statements and such.)

I guess the crux of the problem is this: You can't easily convert a Shape into a Command Button.

Any suggestions? Thanks in advance.

 

[My Aswer Is This]

I always ask people to do this:

When doing something new like putting you script into a shape try something simple

Put this line of code into your shape:

Msgbox "Hello"


Now click on the button and see if you get a message box to popup.

If you do then now you know how to use a shape to run code.

Now as far as your current code you would have to tell us just briefly what this code does.

Does it attempt to change the shape or color of your command button you used before?

But I really do not need to know much but if it's trying to some how modify your old command button then that will cause a problem because you no longer have a command button you have a shape.

 

[michaelch2934]

You're absolutely right. I should try something simple first.
But, to answer your other question... this is basically the Connect 4 game. When it's the Red Player's turn, he is given an option from random possibilities. One is simply to insert another disk in the rack. If he does so, several things happen.
1) Everything is cleared out.
2) We determine whose turn it is, Red or Blue. If Red's turn...
3) Red's TextBox changes interior color (and font) that tells him what to do.
3) His score is changed according to prescribed amounts

I originally had ONE Insert 'Disk Command Button' for both players. But decided to change to one each, alter the name a bit and have the shape be an arrow shaped box. That's where I am now. Trying to put the original Insert Disk Command Button Code in the new DropDiskRed and DropDiskBlue shapes.

Hope this helps clear things up for you. And thanks a million for your exceptional advice.

 

[My Aswer Is This]

Well lets just say your original code looked liked this:

Private Sub CommandButton1_Click()
Cells(1, 1).Value = "George"
End Sub

Well in your shape you would only put in this:

[LEFT][COLOR=#222222][FONT=Verdana]Cells(1, 1).Value = "George"[/FONT][/COLOR][/LEFT]

 

[michaelch2934]

So, you're saying, leave out the 'private Sub CommandButton1_Click()' and the 'End Sub'. Just the raw code and nothing else??

 

[My Aswer Is This]

Well if you added a shape and when you named you script like I mentioned and you named it MY_Script

Your entire script would look like this:

Sub My_Script()
Cells(1, 1).Value = "George"
End Sub

 

[My Aswer Is This]

You would not put in this:

private Sub CommandButton1_Click()​

 

[michaelch2934]

Thanks. So, basically, the VBA code should work the same whether as a Shape or Command Button. Why then does it hang up on that one application? I've tried to do a simple Shape as Macro and it works with simple stuff. But not changing colors of boxes and fonts.

 

[michaelch2934]

Got it!!! After re-reading your post a dozen times... it finally donned on me what I should do. Thanks so very much for spending so much time on this. Have a wonderful day!!

 

[My Aswer Is This]

I really do not know.
It should work the same. Assuming the shape is on the same sheet as the Old Command button was.

Maybe try just running part of the code and see what happens.

 

[michaelch2934]

Will do. Re-assemble it and see what works and what doesn't. But, I'll do that tomorrow. Getting late here in Dallas. Thanks.