Convert a Shape to a Command Button

michaelch2934

New Member
Joined
Sep 17, 2018
Messages
33
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.
 
I wish I had a answer. Will monitor this thread to see how things workout. Time here now 9:13 PM EST.
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.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Is it anything to do with using textboxes and not declaring them?
 
Upvote 0
I tried to recreate your sheet and added text boxes named the same as what you had called them in your code. I got the error 424. Did some googling and believed that the lines tbRedOptionIs = “” Were trying to set the textbox to something out of the object family. I was however using excel 2016 on MacBook. I managed to record some code that achieved what I believed your aim was but the code started growing and I couldn’t figure how to write it cleanly.

Am I correct in thinking there is more code in the module? that determines whether red turn = true etc?
 
Last edited:
Upvote 0
As far as I know a script assigned to a Shape should work the same as a script assigned to a Activex command button. Unless the script somehow refers back to the Command Button which is no longer there.

Again I always start off doing things like this with small amounts of code and if all works well I add more and more to my code. If a error occurs then I can narrow down where the problem may be.

I never write 200 lines of code and then test it out. But that's just me. Unless your sure of your abilities using vba.
 
Upvote 0
Cooper645... yes. Simple button that either Red or Blue selects to indicate it's their turn. This is button for Blue.

Sub BlueTurnButton_Click()

'is blue's turn
BlueTurn = True
RedTurn = False

'change blue button color to "on"
BlueTurnButton.BackColor = 16765783
'change red button color to "off"
RedTurnButton.BackColor = 186
'set varable

'increases blue's turn # by 1
tbBlueTurnNumber.Value = tbBlueTurnNumber.Value + 1
[d37].Value = tbBlueTurnNumber.Value

'clears text boxes
TBWhoseTurnBlue = ""
TBWhoseTurnRed = ""
TBWhatsRedOptionStatic = ""
tbWhatsBlueOptionStatic = ""
tbRedOptionIs = ""
tbBlueOptionIs = ""
TBWhatRedDidStatic = ""
tbWhatBlueDidStatic = ""
tbRedDidWhat = ""
tbBlueDidWhat = ""

'whose turn is blue's
TBWhoseTurnBlue = "It's Blue's Turn"

'reload formula
[d38].Value = "=INDEX(sort!g2:g27,MATCH(d37,sort!h2:h27,0))"

'more than 26, restart game
If tbBlueTurnNumber.Value > 26 Then
tbWhatsBlueOptionStatic.Value = "What can Blue Do?"
tbBlueOptionIs = "limit reached"
Else
tbWhatsBlueOptionStatic.Value = "What can Blue Do?"
tbBlueOptionIs = [d38].Value

End If

'make after blue score same as blue before score
[h25].Value = [H23].Value
[j25].Value = [j23].Value

'open a trap door for disk to fall out of
Range("d17:j17").Clear

Dim cr As Integer
Dim cc As Integer


cr = 17
cc = Int((10 - 4 + 1) * Rnd + 4)

Cells(cr, cc).Interior.Color = RGB(0, 0, 0)


End Sub


Placed this at top of Modules:
Public BlueTurn As Boolean
Public RedTurn As Boolean

Anyway, Hope this helps you understand a little better what I'm trying to do. Thanks for your interest.
 
Upvote 0
Cooper645. I baled on the UserForm method. Too much work to change everything I'd already done. Would it be proper for me to send you the spreadsheet so you can see the whole operation? If not, I understand protocol might disallow that sort of thing.
 
Upvote 0
If these are Command Button names then these same names would have to be the shape names.
And the way their colors are changed may have to be modified also.

BlueTurn = True
RedTurn = False
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top