PDA

View Full Version : Easy way to code all toggle buttons?



classicpcs
08-22-2014, 09:59 AM
I have an spread sheet where I created a toggle button linked to cell that when clicked it shows a check mark and turns green when clicked again it removes checkmark and turns red. Is there any code that will combine all the toggle buttons I want to create? What I am looking at now is coping the same code for each toggle button and then changing the values plus the cell. The code listed below is for one toggle button. Probably need over 100 to do the same thing. Thank you in advance!

Private Sub ToggleButton_Click()
If ToggleButton.Value = True Then
ToggleButton.BackColor = vbGreen 'green
Else
ToggleButton.BackColor = 255 'red
End If
If ToggleButton.Value = True Then
Range("B2").Interior.Color = vbGreen 'green
Range("B2").Value = "YES"
Else
Range("B2").Interior.Color = 255 'red
Range("B2").Value = "NO"
End If
If ToggleButton.Caption = Chr(254) Then
ToggleButton.Caption = Chr(168)
Else
ToggleButton.Caption = Chr(254)
End If

End Sub

snb
08-22-2014, 11:53 AM
Private Sub ToggleButton_Click()
with ToggleButton
.BackColor = iif(.value,vbGreen,vbred)
.Caption = Chr(168- 86 * .value)
Range("B2").Interior.Color = .BackColor
Range("B2")= format(.value,"YES/No")
end with
End Sub

And have a look over here:

http://www.snb-vba.eu/VBA_Sheet_ActiveX_controle_en.html

classicpcs
08-22-2014, 01:16 PM
Thanks, snb but I am getting errors when I try to test. Syntax error. Any suggestions?

snb
08-22-2014, 01:53 PM
I amended the previous code, but you learn a lot by debugging the code yourself...