• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Change Shape Colour

N

novice007

New Member
I am using Excel 2007 and have a spreadsheet that I have a button that when clicked runs a macro that just performs a manual calculation of the workbook. I want to change the colour of the button when it is clicked, and then return it to original colour when the calculations have finished. From what I have heard, you can't change a button colour, but you can change a shape colour. Is it better that I use a shape as the button and, if so, how do I include the shape colour change as part of my macro. I have seen various solutions for this but can't really understand them or get them to work. Can I please just have a simple solution, if possible?
 
Here's a example that should help you out.

[pre]
Code: 
Sub ColorChange()
Dim OriginalColor As Integer
'You need to make a note of what the name
'of your shape/button is
ActiveSheet.Shapes("Star1").Select

'Store original color
OriginalColor = Selection.Interior.ColorIndex

'Change color to something else
Selection.Interior.ColorIndex = 5

'Do stuff here

ActiveSheet.Shapes("Star1").Select
'Change color back
Selection.Interior.ColorIndex = OriginalColor

End Sub
[/pre]
 
Thanks Montrey. Side note of (strange) interest:

Code I provided uses 2 lines to accomplish what should only take 1. Specifically, it selects the shape, and then changes the color. You would think this could be simplified to just

[pre]
Code: 
ActiveSheet.Shapes("Star1").Interior.ColorIndex = 5
[/pre]
but for some reason, VB won't let you do that. =(

If anyone can explain, I'd be happy to learn.
 
Granted, that works, but I'm still curious why the other way doesn't. To turn it into normal sentences (and add some humor), it'd be like:


Willis: "Pick up that box. Paint the object in your hand red." Arnold understands.

vs.

Willis: "Pick up the box and paint it red." Arnold says "What'chu talkin' 'bout, Willis?
 
@Luke


haha nice one! Whatchu talkin about willis.

It looks like interior is not a property of a shape. Shapes use more in-depth properties as they can have multiple interiors. For instance the Pyramid shape has multiple sections.
 
Possibly, but then why does the

[pre]
Code: 
Selection.Interior.ColorIndex
[/pre]
line work?? The Interior suddenly becomes a changeable property, even though we couldn't change it before.
 
Another Excel bug I guess.(because I don't see a logical explanation for the error)


Damn you Microsoft!
 
Thanks for the response and information, however I am having a problem.


Using the macro below, the colour change does not happen, but the Calculate does. What am I doing wrong?


Sub ColorChange()

Dim OriginalColor As Integer

'You need to make a note of what the name

'of your shape/button is

ActiveSheet.Shapes("Rectangle 1").Select


'Store original color

OriginalColor = Selection.Interior.ColorIndex


'Change color to something else

Selection.Interior.ColorIndex = 8


Calculate


ActiveSheet.Shapes("Rectangle 1").Select

'Change color back

Selection.Interior.ColorIndex = OriginalColor


End Sub
 
Dan_L

The best way to know a shapes name is to set it yourself when you make the shape.

Then you control it

eg:

[pre]
Code: 
With myDocument.Shapes.AddShape(msoShapeRectangle, 140, 140, 70, 70)
.Name = "MyShape007"
.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Line.DashStyle = msoLineDashDot
End With
[/pre]
 
Hi ,


The color change does happen ; the fact is , since you are restoring the original color before exiting from the procedure , if the calculate execution is fast enough , you might never notice the color change !


If you really want to see the color change for yourself , just comment out the code lines which restore the original color to the shape. Commenting a code line is done by inserting a single quote ' before the start of the line :

[pre]
Code: 
' ActiveSheet.Shapes("Rectangle 1").Select
' Change color back
' Selection.Interior.ColorIndex = OriginalColor
[/pre]
With this change , the above two lines of code will not be executed , and the rectangle will retain its changed color ; you can now verify whether the color change does take place or not.


Narayan
 
My calculate is not fast, it takes about 15-20sec to perform this, however the colour change still occurs after the calculate, even with the macro simplified as follows:


Sub ColorChange()

Dim OriginalColor As Integer


ActiveSheet.Shapes("Rectangle 1").Select

Selection.Interior.ColorIndex = 8

Calculate


End Sub
 
Hi ,


Why not simplify your procedure even further , by removing the Calculate statement , and seeing what happens ?


If you are now able to see the color change , it means that the screen updating is not taking place till after the Calculate statement has completed executing.


Narayan
 
I removed the calculate and I do see the colour change. But in the original format why does the change happen after the calculate? I can't see why that happens.
 
Hi ,


Thanks for the feedback ; can you insert a DoEvents statement immediately before the Calculate statement and see if it helps ?


Narayan
 
Hi,


Thanks for the tip. I used the DoEvents as suggested, but it still did not work. However, I then combined this with a Wait, and it works how I wanted it to. (I have also tried the Wait by itself and it did not work)


Sub Rectangle1_Click()


ActiveSheet.Shapes("Rectangle 1").Select

Selection.Interior.ColorIndex = 8

DoEvents

Application.Wait Now + TimeValue("00:00:02")

Calculate


End Sub
 
Looks like we just need to force a Screen update to occur.

[pre]
Code: 
Sub Rectangle1_Click()

ActiveSheet.Shapes("Rectangle 1").Select
Selection.Interior.ColorIndex = 8
Application.ScreenUpdating = True

Calculate

End Sub
[/pre]
 
@Luke M

Hi!

Now that you mention it... Many years ago I've used to geta lot of errors with the Shapes & Interior & Colors in VB, it happened in a project where I had to get involved with that for first time. I've tried everything I could imagine, but it still refused to reject everything I did if not applied to a Selection object. Obviously at the hundredth time I insisted in passing thru non-needed intermediate pass, and as Excel didn't let me obviate the .Select and the Selection, from the hundred and one time on, I added automatically them and forgot the issue. But not before blaspheming against the programmer assigned to that part of Excel.

Regards!
 
@SirJB7

Glad to hear I'm not the only one. ;-)


@novice007

Glad to hear it's working!
 
You must log in or register to reply here.
Back
Top