Form button to control checkbox selection

[fergyz]

I have what should be a simple piece of VBA code to put together, but for the life of me i am hitting a brick wall. i am taking a VBA class but the prof is having us write code that isn't in the book or maybe we haven't covered yet.

i have a (form) button that needs to be used to toggle a single checkbox with in the same sheet. the checkbox applies a conditional formatting when selected.

This is the only code i have been able to get to work half-right. i realize it was meant for multiple check boxes but i tried to tailor it to a single.

Sub Hk()

Dim chkBox As Excel.CheckBox
Application.ScreenUpdating = False
For Each chkBox In ActiveSheet.CheckBoxes
chkBox.Value = xlOn
Next chkBox
Application.ScreenUpdating = True
End Sub


i still think there is something even simpler - using the form vs activex button

Thanx

 

[AlphaFrog]

Change the name of the checkbox to suit

Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        If .Value = xlOff Then .Value = xlOn Else .Value = xlOff
    End With
End Sub

Or this...

Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        .Value = IIf(.Value = xlOff, xlOn, xlOff)
    End With
End Sub

 

[Rick Rothstein]

Change the name of the checkbox to suit

Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        If .Value = xlOff Then .Value = xlOn Else .Value = xlOff
    End With
End Sub

Or this...

Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        .Value = IIf(.Value = xlOff, xlOn, xlOff)
    End With
End Sub

Or this (assuming the CheckBox is initialized with either xlOn or xlOff to start with)...

Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        .Value = (xlOn + xlOff) - .Value
    End With
End Sub

 

[fergyz]

Thank you very much. i knew it was a short simple one. i was going to resort to a msgbox to trigger the checkbox.