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.

 

[cooper645]

Unless I am way off base, a macro is VBA code. It is a sub routine. So your VBA code will have a name, which is your macro name....Sub myVBACode ().......so you should just have to right click and assign the macro (your VBA code)

 

[My Aswer Is This]

Here is what you do:

1. Insert a shape on your sheet.
2. Right click on the shape and choose: Assign Macro
3. In the Dialog Box Name your Macro

4. Choose New
5. Now put in your code.
6. Now when you click on the shape your code will run.​

 

[michaelch2934]

Thanks. So, you are saying that if I call a Macro the same name as the Command Button, the Shape (assigned to the Macro) will run exactly like the Command Button. Right?

 

[cooper645]

In the VBA editor you will likely have

Sub CommandButton1_Click ()

change it to Sub somethingMeaningfulOrwhatever ()

then follow the steps in post #3, look for the meaningful name and assign it. Voilà

 

[michaelch2934]

Here is what you do:

1. Insert a shape on your sheet.
2. Right click on the shape and choose: Assign Macro
3. In the Dialog Box Name your Macro

4. Choose New
5. Now put in your code.
6. Now when you click on the shape your code will run.​

Almost works. But, debugger pops up. Doesn't like it now. Why would it work as Command Button but not as Shape?

 

[My Aswer Is This]

If you want a really nice shape to run your script with try this:
From The Ribbon choose Insert Image

Then choose a image from your computer file

Then right click the image and assign macro just like I explained earlier using a Shape.

 

[Trebor76]

Almost works. But, debugger pops up. Doesn't like it now. Why would it work as Command Button but not as Shape?

Unless you're using button specific code like Application.Caller it should work. What line is failing?

 

[My Aswer Is This]

Show us the script you have in the shape.

Almost works. But, debugger pops up. Doesn't like it now. Why would it work as Command Button but not as Shape?

 

[michaelch2934]

This is the code from the original Command Button.
Sub DropDiskRed_Click()
'' Purpose of this button is to indicate that
'' player has dropped a disk in the rack slot
'' and points & slidters are ajusted accordingly '''

'clears old ACTION pts & slids
[d24].Value = 0 'red action pts
[f24].Value = 0 'red action slidters
[H24].Value = 0 'blue action pts
[j24].Value = 0 'blue action slidters

'clears text boxes
tbRedOptionIs = ""
tbBlueOptionIs = ""
tbRedDidWhat = ""
tbBlueDidWhat = ""

'resets tb backcolors to original
tbRedOptionIs.BackColor = 255
tbRedDidWhat.BackColor = 255
tbBlueOptionIs.BackColor = 16711680
tbBlueDidWhat.BackColor = 16711680

'if it's red's turn
If RedTurn = True Then

'display that red inserted a disk
tbRedOptionIs.BackColor = 0
tbRedDidWhat.BackColor = 0
tbRedOptionIs.ForeColor = 16777215
tbRedDidWhat.ForeColor = 16777215
tbRedOptionIs = "What did Red Do?"
tbRedDidWhat = "Inserted a Disk"

'change red pts & slidters for action (insert disk)
[d24].Value = [d24].Value + [d44] 'update red action pts
[f24].Value = [f24].Value + [e44] 'update red action slidters

'change red score for action
[d25].Value = [d23].Value + [d24].Value 'red total pts updated
tbRedPts = [d25].Value 'red slidter BOX updated
[f25].Value = [f23].Value + [f24].Value 'red total slidters updated
tbRedSlidters = [f25].Value 'red pts BOX updated
[f26].Value = [d25].Value + [f25].Value 'red total score updated
tbRedSlipts = [d25].Value + [f25].Value 'red score BOX updated

'now make red before = red after
[d23].Value = [d25].Value
[f23].Value = [f25].Value
End If
'otherwise it's blue's turn to insert a disk

If BlueTurn = True Then
'clear blue side
tbBlueDidWhat = ""

'display that blue inserted disk
tbBlueOptionIs.BackColor = 0
tbBlueDidWhat.BackColor = 0
tbBlueOptionIs.ForeColor = 16777215
tbBlueDidWhat.ForeColor = 16777215
tbBlueOptionIs = "What did Blue Do?"
tbBlueDidWhat = "Inserted a Disk"

'change blue pts & slidters for action (insert disk)
[H24].Value = [H24].Value + [d44]
[j24].Value = [j24].Value + [e44]

'change blue score for action
[h25].Value = [H23].Value + [H24].Value 'blue pts updated
tbBluePts = [h25].Value 'blue points BOX updated
[j25].Value = [j23].Value + [j24].Value 'blue slidters updated
tbBlueSlidters = [j25].Value 'blue slidters BOX updated
[j26].Value = [h25].Value + [j25].Value 'blue slipts score updated
tbBlueSlipts = [h25].Value + [j25].Value 'blue slipts BOX updated

'now make blue before = blue after
[H23].Value = [h25].Value
[j23].Value = [j25].Value

End If

End Sub

Now, it stops on 'resets tb backcolors to original.