Check Box Help

[seasmith]

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!

 

[John_w]

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.

    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]

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]

In that case I wouldn't use a checkbox control. Instead, use the WingDings font to show a checkbox in the cell, like this:

    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]

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]

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:

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]

This worked great!!! Thanks so much for all your help you have no idea how long I have been stuck on this. Thanks again!