CODEWITHSUNDEEP
vbaexcel
NMeneses
NMeneses

Reputation: 172

VBA, Fill shape with button click

I'm very new to macros and I'm trying to teach myself VBA in excel 2013 and could use a lot of help. I would like to know how to change a shape fill color (not a cell) when I click an ActiveX button. Here is what I'm thinking:

onClick()                            <-- Do I need a button name?
if shape.color = RGB(231,230,230)    <-- this is the starting color
    shape.color = RGB(0,0,0)         <-- this is what I want to change it to
else
    shape.color = RGB(231,230,230)   <-- if color is black change to this
end if

By the way, this is for fun and not for anything special or official.

Upvotes: 2

Views: 4940

Answers (3)

Chris Melville
Chris Melville

Reputation: 1518

Put this in the module of the worksheet which contains the button:

EDITED: Sorry, I originally thought you wanted the actual button colour to change. I borrowed from Ralph's answer to account for the shape being the object of the colour change, but below this is for an ActiveX control (as you asked) instead of a form button:

Private Sub CommandButton1_Click() ' Yes, you do need to specify the button
    Dim shp As Shape
    Set shp = ThisWorkbook.Worksheets(1).Shapes("Rectangle 1")
    If shp.Fill.ForeColor.RGB = RGB(231, 230, 230) Then
        shp.Fill.ForeColor.RGB = RGB(0, 0, 0)
    Else
        shp.Fill.ForeColor.RGB = RGB(231, 230, 230)
    End If
End Sub

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

Yes, you need a button name. Drop an ActiveX control on the sheet, then go to the sheet's module in the VBE. In the left dropdown at the top of the code pane, find your control name (you probably want to rename it first - I didn't for this example)

enter image description here

When you pick the control name in the left dropdown and the event name in the right, the VBE stubs the Sub and End Sub for you.

Next you need to reference your shape in your code. Start by using the Me keyword. When you're in a sheet's code module, Me refers to that sheet. Then the dot operator will expose all the properties and methods of that sheet. When you drop an ActiveX control on a sheet, a new property of the sheet is automatically created that provides access to the object. You can use

Me.CommandButton1.BackColor = RGB(231, 230, 230)

You If logic looks fine.

Upvotes: 0

Ralph
Ralph

Reputation: 9434

Here is the code you are looking for:

Option Explicit

Sub ButtonClick()

Dim shp As Shape

Set shp = ThisWorkbook.Worksheets(1).Shapes("Rectangle 1")

If shp.Fill.ForeColor.RGB = RGB(231, 230, 230) Then
    shp.Fill.ForeColor.RGB = RGB(0, 0, 0)
Else
    shp.Fill.ForeColor.RGB = RGB(231, 230, 230)
End If

End Sub

Just make sure that you adjust the above code to the correct shape name (which is "Rectablge 1" in this example, which is located on Sheet(1)).

Afterwards, create a button on that sheet and assign this macro to the button to make it work and toggle the shape color between black and grey.

Next time, I recommend that you (in a first step) record the things you want Excel to do for you with the VBA macro recorder: https://www.youtube.com/watch?v=Q_HQGHPBYoo Afterwards, have a look at the code and try to understand it by highlighting VBA key-words and pressing F1. Also, I'd recommend reading this: http://www.homeandlearn.org/ Afterwards you should be set to write some rather fancy macros.

Upvotes: 3

Related Questions