How to create buttons from scratch that will change colors when clicked on.

[Ancient Wolf]

Needing some help once again.

I have created buttons using the insert shapes function in Excel, but I am now wondering if there is a way to make it look as if the buttons have changed color when they are clicked, and change back when clicked again. I know I will need to have duplicate buttons, and it would be easier to simply use activeX toggle buttons, but I need these buttons to be flashier than any of the form control buttons. Can anyone help me with the coding for this issue? Just in case you need to know, the buttons are assigned macros that control hiding and unhiding of specific rows. That part I have.

 

[Gary McMaster]

I don't know what you mean by "Duplicate Buttons". Are you trying to do something like this?

Gary

Public Sub Test()

'Point any autoshape's "Macro Assign" to this procedure
'Shape must have some interior area rectangle, oval etc (no lines etc)

Dim oShape As Shape

Set oShape = ActiveSheet.Shapes(Application.Caller)

If oShape.Fill.ForeColor.RGB = RGB(255, 0, 0) Then
    oShape.Fill.ForeColor.RGB = RGB(0, 0, 255)
    oShape.TextFrame.Characters.Text = "Hide Row"
    'Hide row code ?
Else
    oShape.Fill.ForeColor.RGB = RGB(255, 0, 0)
    oShape.TextFrame.Characters.Text = "Show Row"
    'Unhide row code ?
End If

End Sub

 

[Ancient Wolf]

Thank you, Gary McMaster. That works just fine. I guess I thought I would have to make two different buttons that controlled the same sections that Excel would switch between when the buttons were clicked. Just out of curiousity, is there a way to do that? I guess it would be switching between two different images.

 

[Gary McMaster]

The autoshapes have a "Visible" property. I suppose you could position two shapes in the same place and toggle visibility.

In the code sample it would be:

oShape.Visible = msoFalse or oShape.Visible = msoTrue

You would have to be a little careful how you set it up. If you lose sync and end up with both invisible ...

Gary

 

[Ancient Wolf]

Thank you again Mr. McMaster, and thank you for the warning. I will be careful.

 

[Gary McMaster]

It may not work as written. My sample does not have to know the name of the shape. It uses Application.Caller which passes the name of the shape that was clicked to my sample.

If you involve two shapes, the procedure(s) will have to know the name of both. When "Button1" goes invisible it must make "Button2" visible before it exits.

Gary

 

[Ancient Wolf]

I see. Thank you. So I guess it wasn't going to be as simple as replacing the color changing coding with the object visible/invisible coding. I still consider myself a newbie with the VBA. Could you show me how it should look?

 

[Gary McMaster]

Well as I mentioned. My original sample uses "Application.Caller" when you click one of the shapes, checkboxes, buttons etc (form type controls) it, in simplified terms, loads the name of the control into memory and passes it to the procedure that you specified in "Assign Macro".

You could have all kinds of Ovals, Triangles, Rectangles or whatever all over the sheet. You would assign the same macro "Test" to all of them.
Because of what I explained above the code selects the correct shape by it's name (I don't even have to know what the name is). The sample code identifies the shape by that name (passed by the sytem) and if it's blue makes it red or vice versa. The identification is handled automatically by the system.

There is a very special syntax that can be used to pass a parameter for the "OnAction" or "Assign Macro". I don't remember exactly what it is but it looks something like this (I hope someone else will bail me out):

"'Test ""Rectangle 1""'"

It is possible that you can only assign the above to a control in code.

This passes a parameter similar to Application.Caller but you would have to enter this syntax in the "Assign Macro" for every single control in your project. Tedious and error prone at best.

Another viable option is to have a seperate procedure for each button. In your shape "Rectangle 1" you would "Assign Macro" "Test1" and in your shape "Rectangle 2" you would "Assign Macro" "Test2". In each of the separate procedures the button would make itself invisible and make its counterpart visible among other things you may want it to do in that procedure (hide / un-hide rows etc).

Again tedious, error prone and it will result much more code, perhaps an overwhelming amount of code if you have more than a handful of controls. It will also be very tedious and difficult to maintain.

A simple example is easy enough if you're a gluten for punishment.

I've used the parameter passing syntax before. I'll see if I can find a working example in my junk drawer in case you're inclined to jump off the cliff

Gary

 

[Gary McMaster]

Could you show me how it should look?

Below is another sample that shows one way of implementing your dual button concept. It may work okay if you don't have dozens or hundreds of shapes. If you used ActiveX you wouldn't have to worry about this name stuff. You could just use the built in events.

You will have to manage all of the shape names by hand. If you change a name in the code you must also change the name of the shape on the worksheet and vice versa.

To try the sample below:

1. Use a new workbook
2. Add 4 rectangles to a worksheet and be sure they are named "Rectangle 1", "Rectangle 2", "Rectangle 3" & "Rectangle 4"
3. Paste the code below into a standard module
4. Assign the macro "Test" to each of the 4 rectangles above.

Hope it helps.

Gary

Option Explicit

Public Sub Test()

Dim oShape As Shape

Set oShape = ActiveSheet.Shapes(Application.Caller)

'If shapes names change on sheet they must be adjusted in this code
'or renamed on the worksheet to match the names in this code

Select Case oShape.Name

    Case "Rectangle 1"
        oShape.Visible = msoFalse
        ActiveSheet.Shapes("Rectangle 2").Visible = msoTrue
        'More code for Rectangle 1
        
    Case "Rectangle 2"
        oShape.Visible = msoFalse
        ActiveSheet.Shapes("Rectangle 1").Visible = msoTrue
        'More code for Rectangle 2
        
    Case "Rectangle 3"
        oShape.Visible = msoFalse
        ActiveSheet.Shapes("Rectangle 4").Visible = msoTrue
        'More code for Rectangle 1
    
    Case "Rectangle 4"
    
        oShape.Visible = msoFalse
        ActiveSheet.Shapes("Rectangle 3").Visible = msoTrue
        'More code for Rectangle 1
        
    Case "Triangle 47"
        'Triangle 47 does not exist yet
        
    Case "Rect Button 1"
        'Rect Button 1 created by "Create_Shape" procedure
        MsgBox "You clicked " & oShape.Name
    'Case As many more case statements as needed
    
    Case Else
    
        MsgBox "Unknown shape name"

End Select

End Sub

Public Sub Show_All_Shapes()

'Make all shapes visible
Dim oShape As Shape

For Each oShape In ActiveSheet.Shapes
    oShape.Visible = msoTrue
Next

End Sub

Public Sub Create_Shape()

'Create a new rectangle and set it to call procedure "Test"
Dim oShape As Shape
Dim oSheet As Worksheet

Dim siTop As Single
Dim siLeft As Single
Dim siWidth As Single
Dim siHeight As Single

Set oSheet = ActiveSheet 'Enable intellisense

siLeft = oSheet.Range("B3").Left
siTop = oSheet.Range("B3").Top
siWidth = oSheet.Range("A1").Width * 2
siHeight = 20

Set oShape = oSheet.Shapes.AddShape(msoShapeRectangle, siLeft, siTop, siWidth, siHeight)

With oShape
    .TextFrame.Characters.Text = "New Rectangle"
    .TextFrame.HorizontalAlignment = xlHAlignCenter
    .TextFrame.VerticalAlignment = xlVAlignCenter
    .TextFrame.Characters.Font.Bold = True
    .TextFrame.Characters.Font.Italic = True
    .TextFrame.Characters.Font.ColorIndex = 3
    .Name = "Rect Button 1"
    .OnAction = "Test" '"Assign Macro" mechanism
    .ZOrder msoBringToFront 'Stacked buttons move forward od backwards instead of visible/invisible?
    'Other properties as needed
End With

End Sub