AutoShape name assigned to macro

[Guest]

I have a worksheet containing two autoshapes (rounded rectangle) allowing the
user to activate or deactivate a macro by clicking on an autoshape. Sub
TurnOff macro is assigned to rounded rectangle with inserted text of "DATA
Mode". Sub TurnOn macro is assigned to rounded rectangle with inserted text
of "POSTER Mode". This code will alternately change the background color of
rounded rectangles, allowing the user to know which mode is activated. e.g.
if DATA Mode is activated, DATA mode rounded rectangle will turn green,
POSTER Mode rectangle will have no fill visible, and vice-versa. To make the
code work correctly, I need to change the "AutoShape 1/2" in the code to
match the actual names of the objects the macros are assigned to. So far,
changing the names to 'DATA Mode', 'POSTER Mode' 'rounded rectangle', or
'rounded rectangle 1/2' have not worked. I continue to receive the error
message "The item with the specified name wasn't found". I would appreciate
any suggestions.

Sub TurnOff()
Application.EnableEvents = False
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.Visible = msoFalse
ActiveSheet.Shapes("AutoShape 2").Select
Selection.ShapeRange.Fill.Visible = msoTrue
myCell.Select
End Sub

Sub TurnOn()
Application.EnableEvents = True
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
ActiveSheet.Shapes("AutoShape 2").Select
Selection.ShapeRange.Fill.Visible = msoFalse
myCell.Select
End Sub

Regards,
Larry

 

[Frank Stone]

hi,
have you given thought to using a button or lable form the
tool box. these can change color too. and i have used both
to run macro and change the captions and colors.
sample code:
Private Sub CommandButton1_Click()
' hide show column
If Columns("L:L").Hidden = True Then
Columns("K:K").Hidden = True
Columns("L:L").Hidden = False
CommandButton1.Caption = "inches"
CommandButton1.BackColor = &H0000FF00& 'red
Else
Columns("K:K").Hidden = False
Columns("L:L").Hidden = True
CommandButton1.Caption = "cms"
CommandButton1.BackColor = &H0000C000& 'green
End If
End Sub
this code does not run a macro. it hides one column and
unhides another. but it does change the caption and the
color of the button.

Regards
Frank

 

[Guest]

Thanks Frank. I'll give it a try. It certainly looks feasible.

Regards,
Larry

 

[Vic Eldridge]

Hi Larry,

I think you might be getting confused between the Autoshape's Text and it's
Name. They are not the same thing. You can see the Shape's name by clicking
on it and the name will appear in the Name Box near the top left of the screen.
This name is what you use to programmatically refer to the shape. You can also
return the name using Application.Caller .

The best advice I can give you though Larry, is to do this job using a Toggle
button from the Control Toolbox toolbar. They are designed for just this kind
thing, and your users will automatically understand what the button is doing.

Regards,
Vic Eldridge

 

[Guest]

Thanks Vic. I forgot that the Autoshape number changes everytime you choose
a different shape. The macros and button color changes now work perfectly.
Here's the finished code:

Sub TurnOff()
Application.EnableEvents = False
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 12").Select
Selection.ShapeRange.Fill.Visible = msoFalse
ActiveSheet.Shapes("AutoShape 11").Select
Selection.ShapeRange.Fill.Visible = msoTrue
myCell.Select
End Sub

Sub TurnOn()
Application.EnableEvents = True
Dim myCell As Range
Set myCell = ActiveCell
ActiveSheet.Shapes("AutoShape 12").Select
Selection.ShapeRange.Fill.Visible = msoTrue
ActiveSheet.Shapes("AutoShape 11").Select
Selection.ShapeRange.Fill.Visible = msoFalse
myCell.Select
End Sub