Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoShape name assigned to macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoShape name assigned to macro
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoShape name assigned to macro
Thanks Frank. I'll give it a try. It certainly looks feasible.
Regards, Larry "Frank Stone" wrote: 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 -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoShape name assigned to macro
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 "Larry" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoShape name assigned to macro
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 "Vic Eldridge" wrote: 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 "Larry" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
autoshape macro | Excel Discussion (Misc queries) | |||
How do I know what macro is assigned to a button? | Excel Discussion (Misc queries) | |||
Which Macro Is Assigned to a Button | Excel Programming | |||
Buttons assigned to a macro | Excel Programming |