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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Mick

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

Regards
 
Upvote 0
Did you paste it in the "Worksheet Module" (not basic Module) with the Togglebuttons ???
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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