Find Autoshape

[Guest]

I have a lot of triangle autoshapes in row 20, that are used as macro
buttons. I want the current button that is being used to change to a
differant color. I have each autoshape programmed to change color when
selected.

What I need is a way for the autoshape to return to its original color when
another autoshape is selected.

Something to the effect;

ActiveSheet.Shapes("Row 20").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid

As always, any help/suggestions is very much appreciated.

 

[Tom Ogilvy]

Either maintain a variable that has a reference to the shape. then use that
in your macro to restore the color

or
At the start of your macro
Loop through all the appropriate shapes and recolor them.

 

[Guest]

Tom -

The first option is what I am looking for becasue there are a lot of
autoshapes. A variable that would reference the shape would be great, but
how do I generate that reference.

Also, how do you identify the autoshape Id or Number?


Thanks a lot for your help.

 

[Tom Ogilvy]

Show your code that colors the Autoshape.

Is it assigned to the autoshape? Do you have a routine for each shape?

 

[Guest]

Tom:

The code I have for (AutoShape 1) is;

Sub SCA()
'
' SCA Macro "Sort Column C Accending"
'
Application.DisplayFullScreen = False
ActiveSheet.Protect UserInterFaceOnly:=True
Range("A19:CX219").Select
Selection.Sort Key1:=Range("C19"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A19").Select

ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Range("c18").Select
Application.DisplayFullScreen = True

End Sub

Yes it is assigned to the AutoShape, as are all.

All Autohapes reside in row 18, two to a cell, so that C18 contains two
triangle AutoShapes; one a triangle the other an upsidedown triangle
(Accending/Decending) each has a macro to do a (Accending/Decending) sort on
the column.

My routine will do the sort and color the sort button "Yellow", so that it
is easy to identify the sort criteria. But when I choose a new sort, the
next AutoShape will change to "Yellow" but now I have two "Yellow" sort
identifiers.

I need to change all Autoshapes back to gray then run my routine.

As Always, Thanks A Lot

 

[Tom Ogilvy]

' at the top of the module outside any procedure.
Public Obj as Object

Sub SCA()
'
' SCA Macro "Sort Column C Accending"
'
Application.DisplayFullScreen = False
ActiveSheet.Protect UserInterFaceOnly:=True
Range("A19:CX219").Select
Selection.Sort Key1:=Range("C19"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A19").Select


If not obj is Nothing then
obj.ShapeRange.Fill.ForeColor.SchemeColor = "whatever
end if
ActiveSheet.Shapes("AutoShape 1").Select
set Obj = Selection
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Range("c18").Select
Application.DisplayFullScreen = True

End Sub

 

[Guest]

Tom:

PERFECT!!! Exactly what I was looking for.

Thanks a lot for your help.