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 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.

 

[cooper645]

Is it anything to do with using textboxes and not declaring them?

 

[michaelch2934]

I'm listening... what do you mean, 'not declaring textboxes'?

 

[cooper645]

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?

 

[My Aswer Is This]

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.

 

[cooper645]

Possibly other buttons going by this other thread

https://www.mrexcel.com/forum/excel...isting-buttons-new-user-form.html#post5183232

or is it now utilising a UserForm?

 

[michaelch2934]

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.

 

[michaelch2934]

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.

 

[My Aswer Is This]

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
​

 

[michaelch2934]

Thanks. Right. BlueTurn is BlueTurnButton_Click. I'll make sure they are the same.