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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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
 

CodeNotDoI

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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
 

CodeNotDoI

New Member
Joined
Feb 12, 2019
Messages
9
This looks really good!! I will try to use it in my scenario and see if I run into any problems. Thanks Dante!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

CodeNotDoI

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,087
Messages
5,466,550
Members
406,489
Latest member
Ankusharma

This Week's Hot Topics

Top