Using Shapes as Toggle Buttons

[CodeNotDoI]

This is originally asked on Excelforum.com here is the link: https://www.excelforum.com/excel-programming-vba-macros/1262270-using-shapes-as-option-buttons.html

The goal is to have about 20 shapes that are tied to each other, where if you select one "On" you can choose which shapes are automatically turned "Off". I don't see anywhere to attach files, but there is an example file on Excelforum link posted by the moderator named Button Attempt copy. This has a sheet of toggle buttons (how I need the shapes to function) and a sheet of shapes with Boolean values. This works with toggle buttons using the code in the example file (it's also the code in the OP), but I need shapes. I realized that if any toggles are hidden and you sort they get distorted. I think using shapes are my only option for this and I haven't been able to make any progress with them.

 

[DanteAmor]

Do you need something like this?

Sub deactivate()
    With ActiveSheet.Shapes("shape1").TextFrame.Characters
        If .Text = "ON" Then .Text = "OFF" Else .Text = "ON"
    End With
    wButtons = Array("shape2", "shape3", "shape4")
    For btn = LBound(wButtons) To UBound(wButtons)
        With ActiveSheet.Shapes(wButtons(btn))
            If .Visible Then .Visible = False Else .Visible = True
        End With
    Next
End Sub


Sub macro_200()
    MsgBox "shape 2"
End Sub
Sub macro_300()
    MsgBox "shape 3"
End Sub
Sub macro_400()
    MsgBox "shape 4"
End Sub

Try the file below:

https://www.dropbox.com/s/e7pta7oqdcealw9/shapes.xlsm?dl=0

 

[CodeNotDoI]

To be sure, I will have to look at this later because I don't have dropbox. I am no expert (you can see by my name), but it doesn't appear this code fits what I'm looking for because I need to specifically choose which buttons are impacted by the button that is clicked. Your code may actually work, but I will have to check later. I'm thinking I may be able to make individual "deactivate" subs based on what I need impacted, then the "macro_" subs be hiding columns/rows instead of msgbox.

 

[DanteAmor]

To be sure of what you need I recommend that you register in dropbox and download the file and review it.
It is also necessary that you put a file and try to explain with examples and images what you need.

 

[CodeNotDoI]

Yes I will try to change your file to meet my needs. Here is a link to an example that I mentioned in the original post:

https://www.dropbox.com/s/2phzcltyiya62je/Button Attempt copy.xlsm?dl=0

The first sheet shows what I want to happen, but it is using toggles and I'm trying to do it with shapes as the "button". The other sheets are less important because they aren't real solutions.

 

[DanteAmor]

Try this

Sub Deactivate()
    
    ActiveSheet.Unprotect
    col1 = ActiveSheet.Shapes("shape1").Fill.ForeColor.RGB
    col2 = ActiveSheet.Shapes("shape11").Fill.ForeColor.RGB
    ActiveSheet.Shapes("shape1").Fill.ForeColor.RGB = col2
    ActiveSheet.Shapes("shape11").Fill.ForeColor.RGB = col1
    '
    v = Application.Caller
    ActiveSheet.Columns("B:Q").EntireColumn.Hidden = False
    Select Case LCase(Application.Caller)
        Case "shape1"
            ActiveSheet.Columns("B:G").EntireColumn.Hidden = True
            ActiveSheet.Shapes("shape1").OnAction = ""
            ActiveSheet.Shapes("shape11").OnAction = "deactivate"
        Case "shape11"
            ActiveSheet.Columns("H:Q").EntireColumn.Hidden = True
            ActiveSheet.Shapes("shape1").OnAction = "deactivate"
            ActiveSheet.Shapes("shape11").OnAction = ""
    End Select
    Range("A1").Select
    ActiveSheet.Protect


End Sub

Try the file below:
https://www.dropbox.com/s/jdg6ak2vg9r41b3/shapes%20toggle.xlsm?dl=0

 

[CodeNotDoI]

This looks really good!! I will try to use it in my scenario and see if I run into any problems. Thanks Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[CodeNotDoI]

I just tried to add one more shape to the code and it did not work. I haven't had a chance to mess with it, but I have had zero luck when tweaking code using shapes. Is it possible to get numerous shapes to work with this structure of code? For instance, have one button on (group of columns showing), but the other two buttons be automatically turned off (hiding the other two groups)? I tried this in the Excel named "Shape2 Added"

https://www.dropbox.com/s/xiyex8o6s4ltixd/Shape2 Added.xlsm?dl=0

 

[DanteAmor]

Try the following code, with this you will be able to add more buttons in an easier way.

Sub Deactivate()
    
    ActiveSheet.Unprotect
    
    For Each shp In ActiveSheet.Shapes
        shp.OnAction = "Deactivate"
        shp.Fill.ForeColor.RGB = 12419407 'blue
    Next
    ActiveSheet.columns("B:Z").EntireColumn.Hidden = False
    
    Select Case LCase(Application.Caller)
        Case "shape1": Call Hide_Column("B:G", "shape1")
        Case "shape2": Call Hide_Column("H:Q", "shape2")
        Case "shape3": Call Hide_Column("R:Z", "shape3")
    End Select
    Range("A1").Select
    
    ActiveSheet.Protect


End Sub


Sub Hide_Column(clms, shape)
    ActiveSheet.columns(clms).EntireColumn.Hidden = True
    ActiveSheet.Shapes(shape).OnAction = ""
    ActiveSheet.Shapes(shape).Fill.ForeColor.RGB = 12566463 'grey
End Sub

https://www.dropbox.com/s/sg67gtq2dupyx2o/shapes toggle v2.xlsm?dl=0