Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA - format a Shape same as a Cell

Posted on 2016-11-17
7
Medium Priority
?
152 Views
Last Modified: 2016-11-18
I'm trying to create a simple Highlight ON/OFF on a Shape.

In the attached example, when the Turn ON button is clicked I want the Shape 'Save' to take the format of the 'On' cell C6. When the Turn OFF is clicked I want it to be like D6, including the Border color as Outline.

(Recording the macro didn't quite give me the answer, as I want to be able to format C6 and D6 in any color, but shapes seem to want to know only RGB)

Thanks.
Toggle-Button.xlsm
0
Comment
Question by:hindersaliva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41892391
What do you mean by the "Turn ON" and "Turn OFF" buttons? I see only one shape on the worksheet named "btnSave".
0
 
LVL 81

Expert Comment

by:byundt
ID: 41892466
I revised your macro so it would toggle the color on the Save button between red and black:
Sub ToggleColor()
With ActiveSheet.Shapes("Rounded Rectangle 1").Fill
        .Visible = msoTrue
        If .ForeColor.RGB = RGB(0, 0, 0) Then
            .ForeColor.RGB = RGB(192, 0, 0)
        Else
            .ForeColor.RGB = RGB(0, 0, 0)
        End If
End With
End Sub

Open in new window

I would also suggest that you check the box for "Require variable declaration" in the Tools...Options...Editor menu item in the VBA Editor. This will put Option Explicit at the top of every newly created module sheet, and is considered good practice in VBA programming. Option Explicit means you must put every variable in a Dim statement. VBA can then catch typos in variable names for you.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41892471
I see that you want to make the Save toggle between the appearance of the shapes at C6 and D6. If so, the code becomes:
Sub ToggleColor()
Dim shp As Shape
With ActiveSheet.Shapes("Rounded Rectangle 1")
        .Fill.Visible = msoTrue
        If .Fill.ForeColor.RGB <> RGB(192, 0, 0) Then
            .Fill.ForeColor.RGB = RGB(192, 0, 0)
            .TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            .Line.Visible = msoFalse
        Else
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .Line.ForeColor.RGB = RGB(0, 176, 240)
            .Line.Visible = msoTrue
        End If
End With
End Sub

Open in new window

If you want to determine the color of the fill or outline by reference to a cell, put the following statements in the Immediate Window of the VBA Editor. They will return the color as a Long. You can then use those values in your code as Color = qqqqqqqq instead of Color.RGB = RGB(xxx, yyy, zzz)
?ActiveCell.Interior.Color
?ActiveCell.Borders.Color
-Toggle-ButtonQ28983915.xlsm
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:hindersaliva
ID: 41892742
Byundt, I get the color 192 for the cell fill color. But the assignment for the Shape is RGB. So wouldn't I need to 'translate/convert' the 192 to RGB in order to apply it to the Shape?

For example, if the cell fill color happens to be 42,875?

Thanks.

Wayne Taylor: sorry I attached before I saved the example! It was just a button to fire off the code.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 41893067
I misspoke on how to use the Color value directly in the code. Since the RGB function returns a Long number, you would use Color.RGB = 42875 to set the color property to a lime green.
Sub ToggleColor()
Dim shp As Shape
With ActiveSheet.Shapes("Rounded Rectangle 1")
        .Fill.Visible = msoTrue
'        If .Fill.ForeColor.RGB <> 192      'RGB(192, 0, 0) Then      'Red
'            .Fill.ForeColor.RGB = 192      'RGB(192, 0, 0)
        If .Fill.ForeColor.RGB <> 42875 Then                'Lime green
            .Fill.ForeColor.RGB = 42875
            .TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = 16777215     'RGB(255, 255, 255)
            .Line.Visible = msoFalse
        Else
            .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = 0    'RGB(0, 0, 0)
            .Line.ForeColor.RGB = 15773696      'RGB(0, 176, 240)
            .Line.Visible = msoTrue
        End If
End With
End Sub

Open in new window

-Toggle-ButtonQ28983915.xlsm
0
 

Author Closing Comment

by:hindersaliva
ID: 41893135
Thanks Byundt
0
 
LVL 81

Expert Comment

by:byundt
ID: 41893163
You may find the following function useful in converting a Long Color into an RGB function call. It takes the Color number, and breaks it down into the R, G and B components. You should install the function in a regular module sheet, just like it was a macro.

To use it, array-enter a formula like the following in three adjacent cells in a row. It should return 167, 123 and 0 in those cells.
=GetRGB(42875)
Function GetRGB(lngColor) As Variant
Dim R As Long, G As Long, B As Long
R = lngColor Mod 256
G = (lngColor - R) / 256 Mod 256
B = (lngColor - R - G * 256) / 256 ^ 2
GetRGB = Array(R, G, B)
End Function

Open in new window

1

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question