Looping Checkboxes on Worksheet

[Guest]

Yet another annoying question..(thanks again Norman for helping me get started)

I have a series of checkboxes (from control toolbox) on a worksheet and need
to pass the values to another worksheet("Data"). The problem I'm having with
the following code is if the value of the checkbox is false the corresponding
cell in "Data" needs to be blank; instead, if there is a total of 6
checkboxes checked , the first 6 cells in "Data" are marked as true.

n = 43
For Each OLEObj In sh.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
If OLEObj.Object.Value Then
n = n + 1
rng.Offset(0, n).Value = Chr(82)
End If
End If
Next OLEObj

 

[Tom Ogilvy]

n = 43
For Each OLEObj In sh.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
n = n + 1
If OLEObj.Object.Value Then
rng.Offset(0, n).Value = Chr(82)
End If
End If
Next OLEObj

 

[Guest]

Grrrr..thanks Tom. I should have seen that.

n = 43
For Each OLEObj In sh.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
n = n + 1
If OLEObj.Object.Value Then
rng.Offset(0, n).Value = Chr(82)
End If
End If
Next OLEObj