Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
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" wrote: 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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
Show your code that colors the Autoshape.
Is it assigned to the autoshape? Do you have a routine for each shape? -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
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" wrote: Show your code that colors the Autoshape. Is it assigned to the autoshape? Do you have a routine for each shape? -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
' 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 -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: Show your code that colors the Autoshape. Is it assigned to the autoshape? Do you have a routine for each shape? -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Autoshape
Tom:
PERFECT!!! Exactly what I was looking for. Thanks a lot for your help. "Tom Ogilvy" wrote: ' 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 -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: Show your code that colors the Autoshape. Is it assigned to the autoshape? Do you have a routine for each shape? -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Ronbo" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autoshape | Excel Discussion (Misc queries) | |||
Autoshape | Excel Discussion (Misc queries) | |||
NAME OF AUTOSHAPE | Excel Discussion (Misc queries) | |||
AUTOSHAPE | Excel Programming | |||
autoshape | Excel Programming |