Multiple Toggle Buttons VBA Code

IannW

New Member
Joined
Aug 25, 2018
Messages
18
Hi All

I have a spreadsheet with a row of Toggle Buttons. The toggle buttons are to indicate if a task has been completed. When clicking the button it says 'YES' and using conditional formatting, turns the whole row green.

I have some VBA attached to the toggle button to determine the colour when true of false, see below:

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
ToggleButton1.BackColor = RGB(173, 219, 123)
ToggleButton1.Caption = "Yes"
Else


ToggleButton1.BackColor = RGB(255, 204, 204)
ToggleButton1.Caption = "No"
End If

End Sub

My question is this. When I add another toggle button, I copy the same code into VBA but have to edit the toggle button name to ToggleButton2, but as you can see, this has to be done 6 times in the code. I want to keep adding to the list of tasks but it takes ages to edit the code each time.

Is there a way to attach the VBA code to a new toggle button and have the name automatically increment each time?

Help gratefully received.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,012
Office Version
  1. 2013
Platform
  1. Windows
You said:
I have a spreadsheet with a row of Toggle Buttons.

Do you really have a row of Toggle Buttons.

Each Row has 16,456 cells.
I doubt you want 16,456 Toggle buttons

Do you have 1 task on a separate Row or a separate column

Like do you have

Clean House in Range("A1")
Clean Garage in Range("A2")
Cook Mr. Excel a Cake in Range("A3")

Or do you have

Clean House in Range("A1")
Clean Garage in Range("B1")
Cook Mr. Excel a Cake in Range("C1")

Instead of needing a lot of Toggle buttons. Why not just double click on a cell.

When the task is completed we turn the cells interior color Green.

So if you have "Cut Grass" in Range("A3") and you double click on Cut Grass then that cell will turn Green

This is a lot easier then using a Toggle Button
Would this work for you?
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You could add the sub "Tog" and pass the ToggleButton name as shown below.
Just add the next Toggle button and adjust one line of code.
Code:
Option Explicit

Private [COLOR="Navy"]Sub[/COLOR] ToggleButton1_Click()
 Call Tog(ToggleButton1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ToggleButton2_Click()
Call Tog(ToggleButton2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]



[COLOR="Navy"]Sub[/COLOR] Tog(Tognam)
    [COLOR="Navy"]If[/COLOR] Tognam.Value = True [COLOR="Navy"]Then[/COLOR]
        Tognam.BackColor = RGB(173, 219, 123)
        Tognam.Caption = "Yes"
    [COLOR="Navy"]Else[/COLOR]
        Tognam.BackColor = RGB(255, 204, 204)
        Tognam.Caption = "No"
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,287
Office Version
  1. 365
Platform
  1. Windows
Another option, if you are willing to change the Toggles to Form Control checkboxes, you could assign this macro to all of them
Code:
Sub IannW()
   Dim Shp As Shape
   Set Shp = ActiveSheet.Shapes(Application.Caller)
   If Shp.ControlFormat = 1 Then
      Shp.Fill.ForeColor.RGB = RGB(173, 219, 123)
      Shp.TextFrame.Characters.Caption = "Yes"
   Else
      Shp.Fill.ForeColor.RGB = RGB(255, 204, 204)
      Shp.TextFrame.Characters.Caption = "No"
   End If
End Sub
 

IannW

New Member
Joined
Aug 25, 2018
Messages
18

ADVERTISEMENT

Hi

Clean House in Range("A1")
Clean Garage in Range("A2")
Cook Mr. Excel a Cake in Range("A3")

Can you double click to turn green and add text, ie, No, Yes? Is this done through conditional formatting? I also want the ability to count completed rows as well. I currently do this by using a COUNTIF to count the TRUE from the toggle button (linked to another cell).

Regards
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,012
Office Version
  1. 2013
Platform
  1. Windows
So if you double click on Clean House and then have it enter Yes or No you will now not see Clean House.
And how would the script know to enter Yes or No. And then count what? all Yes's or all No's
And when would the cell turn Green? When you enter Yes or No.

It's looks like to me if the Task was not done we would do nothing. And we could count how many times the cell in column A is not Green.

Why do we need Yes or No

If the cell is not Green then the job has not been done.



Hi

Clean House in Range("A1")
Clean Garage in Range("A2")
Cook Mr. Excel a Cake in Range("A3")

Can you double click to turn green and add text, ie, No, Yes? Is this done through conditional formatting? I also want the ability to count completed rows as well. I currently do this by using a COUNTIF to count the TRUE from the toggle button (linked to another cell).

Regards
 

IannW

New Member
Joined
Aug 25, 2018
Messages
18

ADVERTISEMENT

Hi

Sounds good. Can you specify a range of cells that this applies to? I tried the below code I found and it works ok but on the whole worksheet.

Am new to this so thanks for your patience.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.Color = vbRed
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.Color = vbGreen
End Sub
 

IannW

New Member
Joined
Aug 25, 2018
Messages
18
Mick

This looks a great option, however, I copied and pasted the code but it doesn't work.

Regards
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Did you paste it in the "Worksheet Module" (not basic Module) with the Togglebuttons ???
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,012
Office Version
  1. 2013
Platform
  1. Windows
You first seemed like you liked my Double click option.
If your still interested try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Sounds like you want to toggle color from Green to Red

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/9/2019  9:04:22 AM  EDT
If Target.Column = 1 And Target.Value <> "" Then
Cancel = True
With Target.Interior
Select Case Target.Interior.ColorIndex
    Case xlNone
        .ColorIndex = 4
    Case 4
        .ColorIndex = 3
    Case 3
        .ColorIndex = 4
End Select
End With
End If
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,328
Messages
5,547,278
Members
410,783
Latest member
sonnny
Top