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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. 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
12,622
Office Version
  1. 2007
Platform
  1. 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

ADVERTISEMENT

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
12,622
Office Version
  1. 2007
Platform
  1. 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

ADVERTISEMENT

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
12,622
Office Version
  1. 2007
Platform
  1. 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
12,622
Office Version
  1. 2007
Platform
  1. 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,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

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
Top