Check Box Help

seasmith

New Member
Joined
Jul 6, 2011
Messages
44
I new to this site and fairly new to VBA. I have a form that has a checkbox on it, and when the checkbox is checked in the form I would like to have a checked checkbox appear in a certain cell, and if the checkbox is not checked have an unchecked checkbox appear. I have been searching the internet to try and find a solution and have not found anything. This is my last hope. Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,531
Manually put a checkbox (from the Controls Toolbox) in the cell and make it invisible. Then use something like this to look for the checkbox (named CheckBox1, for example), make it visible and set it to ticked.
Code:
    Dim objOLE As OLEObject
    
    For Each objOLE In ActiveSheet.OLEObjects
        If objOLE.Name = "CheckBox1" Then
            objOLE.Object.Value = True
            objOLE.Visible = True
        End If
    Next
 

seasmith

New Member
Joined
Jul 6, 2011
Messages
44
This works great!! Thanks so much. There is just another slight problem they I forgot to mention in the initial question. This is for a database so each time I want to enter new data the data entered in the form goes to the next empty row. If I put empty check boxes in the same column is there anyway the next time I enter data is goes to the next hidden checkbox?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,531
In that case I wouldn't use a checkbox control. Instead, use the WingDings font to show a checkbox in the cell, like this:
Code:
    Dim nextEmptyRow As Long
    
    nextEmptyRow = 10     'arbitrary row number for demonstration purposes
    
    With Cells(nextEmptyRow, "A")           'column A
        .Font.Name = "Wingdings"
        .Value = "o"            'Empty checkbox in cell
        .Value = "þ"            'Ticked checkbox in cell
        .Value = ""             'Empty cell
    End With
 

seasmith

New Member
Joined
Jul 6, 2011
Messages
44

ADVERTISEMENT

Using wingdings is a great idea! But...I can't seem to get the code you have given me to work. I have a button that when pressed it opens up a form with many different fields to fill in information.

One of the fields is Location and I use this code to find the next empty row:

Range("A65536").End(xlUp)(1, 3) = txtLocation.Text 'Prints the text in the Location column'

That is for a text box and it prints the text to the third column. Is there anyway I can use this type of code with a checkbox? If so is there a way to combine the code you just gave me to use windings with that type of code above that I have been using?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,531
It might be something like this, assuming (I might be wrong) that your userform has a command button (CommandButton1) which, when clicked, transfers the userform field values to the next row in the active sheet:
Code:
Private Sub CommandButton1_Click()

    Dim nextEmptyRow As Long
    
    With ActiveSheet
    
        'Find next empty cell in column A
        
        nextEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If .Cells(nextEmptyRow, "A").Value <> "" Then nextEmptyRow = nextEmptyRow + 1

        'Put location text in column C
        
        .Cells(nextEmptyRow, "C").Value = txtLocation.Text
        
        'Put checkbox in column A
        
        If CheckBox1.Value Then
            'CheckBox1 is ticked
            .Cells(nextEmptyRow, "A").Font.Name = "Wingdings"
            .Cells(nextEmptyRow, "A").Value = "þ"           'Ticked checkbox in cell
        Else
            'CheckBox1 is not ticked
            .Cells(nextEmptyRow, "A").Font.Name = "Wingdings"
            .Cells(nextEmptyRow, "A").Value = "o"            'Empty checkbox in cell
        End If

    End With

End Sub
 

seasmith

New Member
Joined
Jul 6, 2011
Messages
44
This worked great!!! Thanks so much for all your help you have no idea how long I have been stuck on this. Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,476
Messages
5,572,347
Members
412,459
Latest member
asmi_1758
Top