Streamline VBA to Change shape color on click in excel

Question

I have a ‘form’ that contains set of questions on a worksheet (note this is not a userform and I don’t want to use one). Some answers are yes/no, others have multiple answers such as quantity (ie and the answer may be 1 or 2 or 3 or 4 etc). The design of the ‘form’ on this worksheet calls for these answers to be shapes which the user clicks like a button to select their answer - Pls note I do not want to use Command buttons. In this simple example I have 2 rectangle shapes one name “yes” and one name “no” When user clicks “yes”, the color fill of the shape changes to blue (and the “no” shape stays white). If user clicks “no” , the “no” shape turns blue, and “yes” goes white. It also populates and answer in A1 in this example. I use the following code which works fine (although im sure could be cut down somewhat) however the problem comes when I need to replicate this code multiple times. For example, if I have a question that has multiple answers like Quantity (answers could be 1 or 2…

Accepted Answer

yes, you're right, you could write a Sub with your shape as an input and eventually fill it with the "yes" and "no" events. E.g. `ClickOnButton MyShape, YesNo` where YesNo can be a flag that triggers one of the events. Then you could call that Sub for each button.

I also would suggest the use of some `With`s: `With Activesheet.MyShape` is going to do fine. Finally, please do not use the `.Select`. There are tons of reason not to do that and most of all the select won't do really anything in your code... Well yeah, slow it down.

I'll give you an example to try to explain better: You could write a subroutine giving a Shape and a Boolean (for example) as an Input (that would be the `YesNo` variable). Inside the subroutine you could write the 2 different behaviours conditionally (`If` ... `Else` ... `End If`) to the `YesNo` variable (or, do we want to call it `GreenRed`/`ActiveInactive`?). In both conditions you can write whatever you want. The following can be used for both "yes" and "no" buttons.

```
Sub Example(YourShape As Shape, GreenRed as Boolean)

If GreenRed = True Then ' Say we want in this case an "active" button
        With YourShape
            .Fill.ForeColor.RGB = RGB(85, 142, 213)
            .Line.BackColor.RGB = RGB(198, 217, 241)
            .TextFrame.Characters.Font.Color = RGB(255, 255, 255)
        End With
    Else
        With YourShape
            .Fill.ForeColor.RGB = RGB(255, 255, 255)
            .Line.BackColor.RGB = RGB(198, 217, 241)
            .TextFrame.Characters.Font.Color = RGB(85, 142, 213) 
        End With
    End If

End Sub
```

You can then in your Main program write `Example ActiveSheet.Shapes("yes"), True` to get a button activate itself and `Example ActiveSheet.Shapes("no"), False` to deactivate the other.

Streamline VBA to Change shape color on click in excel

Solution

Problem