Multiple Toggle Buttons VBA Code

[IannW]

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.

 

[My Aswer Is This]

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?

 

[MickG]

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.

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

 

[Fluff]

Another option, if you are willing to change the Toggles to Form Control checkboxes, you could assign this macro to all of them

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]

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]

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]

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]

Mick

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

Regards

 

[MickG]

Did you paste it in the "Worksheet Module" (not basic Module) with the Togglebuttons ???

 

[My Aswer Is This]

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

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