Using Shapes as Toggle Buttons

CodeNotDoI

New Member
Joined
Feb 12, 2019
Messages
9
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Do you need something like this?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try this

Code:
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
 
Upvote 0
This looks really good!! I will try to use it in my scenario and see if I run into any problems. Thanks Dante!
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
Try the following code, with this you will be able to add more buttons in an easier way.


Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top