Disable Shape Macro

[Ark68]

Is it possible to disable a macro assigned to a shape?

I have a series of shapes substituting for radio buttons. Each shape has is associated to a value. What I am hoping to be able to do is remove the functionality (provided by the macro) of the shape when clicked.

So, if cell D5 = 0, then disable the macro on named shape CUEDR.

 

[VoG]

Try

If Range("D5").Value = 0 Then Exit Sub

 

[Ark68]

Hi Peter ... that would work, but I'm not sure how I could integrate that into my scenario.

Excel 2010
	Y	Z	AA	AB	AC	AD	AE
14	RIM Late			Kelcey			RPA
15	1:30 PM - 9:30 PM
16	DR	DT	FR	FT	CR	CT	Total
17	7	0	15	0	0	0	22
18
19
Workorders

It doesn't show in the attachment, but cells Y18:AE18 have individually named shapes with a the macro 'toggle' associated to it's click. (basically the macro toggles the color for ON vs OFF and creates a list in wshvar.range("T3:xx) ). In the example I provided, the shapes in Z18, AB18, AC18 and AD18 would be "disabled". The code highlighted in green below is the portion of the macro that deals wiuth the selectiopn of individual shapes.

The With my current unrefined code (in blue), when the user clicks the shape in AE18, it sets all the shapes Y18:AE18 to ON. Similarly, I will also need to find a means to disable the appropriate shapes when the user opts ALL (AE18). With the code now, in addition to the shading which will need to be addressed, the values that make up the array have to be based on only the valid non-zero related buttons.

Sub toggle()

    Dim myDocument As Worksheet
    Dim wshvar As Worksheet
    Dim rtarget As Variant
    Dim nextrow As Integer
    Dim p1 As Variant
    'Dim w1 As String

    Set wshvar = Worksheets("varhold")
    Set myDocument = Worksheets("Workorders")
    Set rtarget = myDocument.Shapes(Application.Caller)
    
    
    
    nextrow = wshvar.Cells(Rows.Count, "T").End(xlUp).Row + 1
    If rtarget.Name = "CUEALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT", "CUEALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT"))
        Else
            myDocument.Shapes.Range(Array("CUEDR", "CUEDT", "CUEFR", "CUEFT", "CUECR", "CUECT", "CUEALL")).Fill.ForeColor.RGB = RGB(255, 255, 255) 'ON to OFF
        End If
        
    ElseIf rtarget.Name = "CULALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("CULDR", "CULDT", "CULFR", "CULFT", "CULCR", "CULCT", "CULALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("CULDR", "CULDT", "CULFR", "CULFT", "CULCR", "CULCT"))
        Else
            myDocument.Shapes.Range(Array("CULDR", "CULDT", "CULFR", "CULFT", "CULCR", "CULCT", "CULALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
   
    ElseIf rtarget.Name = "HPEALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("HPEDR", "HPEDT", "HPEFR", "HPEFT", "HPECR", "HPECT", "HPEALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("HPEDR", "HPEDT", "HPEFR", "HPEFT", "HPECR", "HPECT"))
        Else
            myDocument.Shapes.Range(Array("HPEDR", "HPEDT", "HPEFR", "HPEFT", "HPECR", "HPECT", "HPEALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    ElseIf rtarget.Name = "HPLALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("HPLDR", "HPLDT", "HPLFR", "HPLFT", "HPLCR", "HPLCT", "HPLALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("HPLDR", "HPLDT", "HPLFR", "HPLFT", "HPLCR", "HPLCT"))
        Else
            myDocument.Shapes.Range(Array("HPLDR", "HPLDT", "HPLFR", "HPLFT", "HPLCR", "HPLCT", "HPLALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    ElseIf rtarget.Name = "RPEALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("RPEDR", "RPEDT", "RPEFR", "RPEFT", "RPECR", "RPECT", "RPEALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("RPEDR", "RPEDT", "RPEFR", "RPEFT", "RPECR", "RPECT"))
        Else
            myDocument.Shapes.Range(Array("RPEDR", "RPEDT", "RPEFR", "RPEFT", "RPECR", "RPECT", "RPEALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    ElseIf rtarget.Name = "RPLALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("RPLDR", "RPLDT", "RPLFR", "RPLFT", "RPLCR", "RPLCT", "RPLALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("RPLDR", "RPLDT", "RPLFR", "RPLFT", "RPLCR", "RPLCT"))
        Else
            myDocument.Shapes.Range(Array("RPLDR", "RPLDT", "RPLFR", "RPLFT", "RPLCR", "RPLCT", "RPLALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    ElseIf rtarget.Name = "WPEALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT", "WPEALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"))
        Else
            myDocument.Shapes.Range(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT", "WPEALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    ElseIf rtarget.Name = "WPLALL" Then
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then
            myDocument.Shapes.Range(Array("WPLDR", "WPLDT", "WPLFR", "WPLFT", "WPLCR", "WPLCT", "WPLALL")).Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"))
        Else
            myDocument.Shapes.Range(Array("WPLDR", "WPLDT", "WPLFR", "WPLFT", "WPLCR", "WPLCT", "WPLALL")).Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    
    Else
        If rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) Then 'OFF to ON
            rtarget.Fill.ForeColor.RGB = RGB(255, 0, 0)
            wshvar.Range("T" & nextrow) = rtarget.Name
        Else
            rtarget.Fill.ForeColor.RGB = RGB(255, 255, 255) 'ON to OFF
            p1 = wshvar.Range("T3:T500").Find(What:=rtarget.Name).Address
            wshvar.Cells.Range(p1).Delete xlUp
            
        End If
    End If

End Sub