VBA help with check and uncheck

I have a code to put data in a specific cell range, however, when I uncheck the checkbox, it doesn't remove the text. I'm really new to this and surprised I got as far as I did, any help and I would be so grateful! Thanks

Sub CheckBox1_Click()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
'Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LRange = "B" & CStr(LRow)
'Change text in column b, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = ""
'Clear text in column b, if checkbox is unchecked
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
If ws.Range("B15").Value = "" Then
Set firstEmptyCell = ws.Range("B15")
Else
If ws.Range("B16").Value = "" Then
Set firstEmptyCell = ws.Range("B16")
Else
Set firstEmptyCell = ws.Range("B15").End(xlDown).Offset(1)
End If
End If
If firstEmptyCell.Row < 41 Then
firstEmptyCell.Value = "3300-0401"
Else
MsgBox "There aren't any empty cells in range B15:B40."
End If
Else
ActiveSheet.Range(LRange).Value = Null
End If
End Sub

Not bad code for a self proclaimed noob. Well done.

TIP: When posting code in the forum, surround your code with [Code] tags. Seel the # icon in the forum editor.


When do you want to remove the text? Your code fills B15:B41 every time it is checked. If you remove the last entry when you uncheck it, the range will not fill up. Is that what you want? Can you better describe the action and events you want to happen?

Is the Checkbox also on "Sheet1" ?

Thanks, but I did steal some info from these type of sites :-)

I want to remove the unchecked text. Let's say I check the box and I change my mind, I want to uncheck the box and have the text removed. Yes, Sheet1. If you can help, it would finish what I'm trying to accomplish and I would be a very happy noob!

Originally Posted by akajj13

Thanks, but I did steal some info from these type of sites :-)

I want to remove the unchecked text. Let's say I check the box and I change my mind, I want to uncheck the box and have the text removed. Yes, Sheet1. If you can help, it would finish what I'm trying to accomplish and I would be a very happy noob!

I'm trying to figure out what you want. I don't know what "remove the unchecked text" means.

This is a guess. It toggles text in column B for the same row as the checkbox.

Sub CheckBox1_Click()
    Dim cBox   As CheckBox
    Set cBox = ActiveSheet.CheckBoxes(Application.Caller)
    With Range("B" & cBox.TopLeftCell.Row)
        If cBox.Value = xlOn Then
            .Value = "3300-0401"
        Else
            .ClearContents
        End If
    End With
End Sub

Thank you,I will give it a try. When I check a box, it gives me a value,I need to have the value removed when I uncheck the checkbox. Sorry, I'm new to this and don't quite know how to explain myself with the correct words/jargon. Thanks again!

Originally Posted by akajj13

Thank you,I will give it a try. When I check a box, it gives me a value,I need to have the value removed when I uncheck the checkbox. Sorry, I'm new to this and don't quite know how to explain myself with the correct words/jargon. Thanks again!

I get the add text when checked and remove text when unchecked part. The part that wasn't clear is add\remove text from what cell? Your original code filled a range of cells from B15:B41.

I wish I could send you the spreadsheet. I want to have the checkbox fill b15:b40. 3300-041 is the checkbox text. Once again, I'm sorry!