Return a cell value to null using VBA 1

[jrobin5881]

I have a spreadsheet that is functioning basically as a surevey type of data form. I have sections of the spreadsheet whereby checkboxes are grouped under a category heading. On a different worksheet each of the category headings are stored in Row 1 to serve as field names. On the change event for each of the checkboxes the selection name finds the next blank cell under the category name and populates the cell. I have that working just fine.

Here's the issue. I need the selection name that populated the cell under the category name to be deleted if the user de-selects the check box. I can't seem to get that to happen with the coding I'm using. I tried recording a macro where I did a find and replace with a blank but that seems to be a hard way to go. I'm sure there must be a much easier way. I posted a sample of my code below and you can see where I was testing some code that would run on the False value but its hardcoded to a specific cell that it may or may not be in. The one commonality is that each response brought over will be in the same column.

Private Sub CheckBox1_Change()
Dim NextRow As Integer

If CheckBox1.Value = True Then
NextRow = Worksheets!Sheet2.Range("f65536").End(xlUp).Row + 1
Application.Worksheets("Sheet2").Cells(NextRow, 6) = "Eastern States"
End If

'If CheckBox1.Value = False Then
'Application.Worksheets("Sheet2").Cells(2, 6) = " "
'End If

End Sub


Private Sub CheckBox2_Change()
Dim NextRow As Integer

If CheckBox2.Value = True Then
NextRow = Worksheets!Sheet2.Range("f65536").End(xlUp).Row + 1
Application.Worksheets("Sheet2").Cells(NextRow, 6) = "Western States"
End If

'If CheckBox2.Value = False Then
'Application.Worksheets("Sheet2").Cells(2, 6) = " "
'End If

End Sub


Private Sub CheckBox3_Change()
Dim NextRow As Integer

If CheckBox3.Value = True Then
NextRow = Worksheets!Sheet2.Range("f65536").End(xlUp).Row + 1
Application.Worksheets("Sheet2").Cells(NextRow, 6) = "Northern States"
End If
End Sub

 

[SkipVought]

Hi,

Use the ClearContents method

If CheckBox1.Value = False Then
   Worksheets("Sheet2").Cells(2, 6).ClearContents
End If

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[rmikesmith]

Hi Skip,

Honestly, I'm not sure, but I think the problem is that there is no "mapping" of a cell that was populated via a checked CheckBox to that CheckBox. When the CheckBox is unchecked, the code can't determine which cell it originally populated (since this was chosen as the next blank cell).

jrobin5881: Is that correct or if not can you clarify?

Regards,
Mike

 

[jrobin5881]

Mike,

You are correct. I left the code cells(2,6)="" to show that I tested it and it didn't work. How will it find the cell originally populated?

I tried recording the keystrokes for find and replace but that creates bigger issues.

 

[rmikesmith]

I think you have several options:

1. Set up your survey sheet so that there is a one-to-one correspondence between the checkboxes and specific worksheet cells where the info will be populated. In this case you would hard-code these cell addresses into the CheckBox Change events. [Without fully understanding your layout, I don't know whether this is appropriate for your situation]

2. Create a dynamic map that points to the cell populated by each CheckBox when checked. The Change event code can then use this to clear the proper cell when the CheckBox is unchecked. Not as complicated as it sounds.

3. Use the Find method of the Range object to locate the text populated by checking a given CheckBox. Assumes a unique string (text) for each CheckBox selection. Sounds like you may have tried this or a version of it. Don't know what problem you ran into but I'm sure this approach can work, also.


Regards,
Mike

 

[jrobin5881]

Mike,

The first option does not work because you can have more than one response to certain questions and only one to others. Eventually the summary sheet will be combined with other responses to form a database for more analysis. If I use option 1 concievably I could have a response in Cells(2,3) and Cells(2,7) and Cells(2,9) which would give me blank cells in between.

Option 2 seems to be best. Where can I find out how to create a dynamic map?

 

[rmikesmith]

Here's what I did:

1. Insert a new worksheet (in my example I gave this the name wksMap). After everything is prepared, this sheet can be hidden (In the VB Editor, select this sheet in the Project Explorer, scroll down to the Visible property in the Properties window then select 2-xlSheetVeryHidden).

2. Add 2 headings, CheckBox (Column A) and Mapped Cell Address (Column B). Note, these are just indicators for your use. In fact Column A won't be used by the code at all. It's just a way to keep track of the correspondence between CheckBox and cell on the map worksheet.

3. Add numbers 1 through n for CheckBoxes 1 - n in Column A. Again, this is just a visual aid and not needed for the code to work.

4. Switch to the survey worksheet code module, where your Change event procedures are located. Make the procedure code look like the following (example is for CheckBox1):

Private Sub CheckBox1_Change()

   If CheckBox1.Value = True Then
     With ThisWorkbook.Worksheets("Survey")
       NextRow = .Cells(65536, 6).End(xlUp).Row + 1
       .Cells(NextRow, 6) = "Eastern States"
       Worksheets("wksMap").Cells(2, 2).Value = .Cells(NextRow, 6).Address
     End With
   Else
     ThisWorkbook.Worksheets("Survey").Range(ThisWorkbook.Worksheets("wksMap").Cells(2, 2).Text).ClearContents
     ThisWorkbook.Worksheets("wksMap").Cells(2, 2).ClearContents
   End If

End Sub

Note, I named the worksheet containing the CheckBoxes "Survey" so be sure to change to whatever you actually used.

It's pretty straightforward. You are hard-coding the relationship between a CheckBox and a storage location that indicates where the checked CheckBox put information on the survey worksheet. The code can then look up this location and clear it. To see this in action, make some CheckBox selections/unselections and switch to the map worksheet (do this before hiding it).

This strategy could be accomplished in other ways, such a using an array or collection but the worksheet approach has the benefit of retaining the information between Excel sessions.


Regards,
Mike

 

[jrobin5881]

Mike,

Thank you very much. With your help we've created a document that will go to about 3,000 end users and serve as an excellent management tool and I couldn't have done it without you.