Form button to control checkbox selection

fergyz

New Member
Joined
Jan 30, 2018
Messages
7
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,194
Change the name of the checkbox to suit

Code:
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...
Code:
Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        .Value = IIf(.Value = xlOff, xlOn, xlOff)
    End With
End Sub
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
Change the name of the checkbox to suit

Code:
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...
Code:
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)...
Code:
Sub Hk()
    With ActiveSheet.CheckBoxes("[COLOR=#ff0000]Check Box 2[/COLOR]")
        .Value = (xlOn + xlOff) - .Value
    End With
End Sub
 
Last edited:

fergyz

New Member
Joined
Jan 30, 2018
Messages
7
Thank you very much. i knew it was a short simple one. i was going to resort to a msgbox to trigger the checkbox.
 

Forum statistics

Threads
1,085,834
Messages
5,386,226
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top