Using a loop to determine if a checkbox is checked?

[lcb34]

Hi,

I'm trying to write code that will loop through a set of checkboxes, determine which are checked or unchecked, and then write the corresponding value of the unchecked boxes to a new sheet. My trouble is writing the code to loop thru the boxes because I am trying:

Dim i As Integer

For i = 1 To i = 15
If CheckBox"i".Value = True Then

i = i + 1

This way, if a checkbox is checked, the loop will just move on to the next one. However, I don't know how to reference checkbox1 in the code without explicitly writing it. I know you can reference a cell by saying "A" & i but I don't know the correct syntax for a checkbox. Can anyone help me???

 

[dave3009]

Hi and Welcome

Dim i As Integer

For i = 1 To 15
If Controls("CheckBox" & i).Value = True Then

'...do whatever needs doing if that checkbox is checked

Next i

 

[lcb34]

I tried that code and it tells me that 'A sub or function is not defined' and highlights Controls

 

[Sektor]

Where are you checkboxes located: UserForm or worksheet? And write its name.

 

[Weaver]

Dim i As Integer

For i = 1 To i = 15
If CheckBox"i".Value = True Then

i = i + 1

Don't do this

However you wind up checking your checkboxes, if you don't want to do anything if it's checked (that's what I'm assuming here) then you should create an if statement that only runs the code if the checkbox is false.

for ...
if ... = false then
'do things here
end if
next

Adding 1 to i yourself will just increment twice, as the loop will advance when you hit next in either case, so you'll end up skipping checkboxes.

 

[lcb34]

Everything is in a worksheet. I've managed to get the code to work, but by copying this code over and over:
<code>
Dim LastCell As Range
If CheckBox1.Value = True Then

With Sheets("2")
Set LastCell = .Cells(.Rows.Count, "D").End(xlUp)
If IsEmpty(LastCell) Then
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = Sheets("1").Range("H4").Value

Else

With Sheets("2")
Set LastCell = .Cells(.Rows.Count, "E").End(xlUp)
If IsEmpty(LastCell) Then
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = Sheets("1").Range("H4").Value

End If


</code>


And so on....I have at least 75 checkboxes so its a lot of code. What I am trying to do is create a document that will allow the user to input details and things to do to prepare for an important meeting, and then run a report to tell them what has been completed and what still needs to be done. The functionality I'm trying to get would allow the user to enter a new meeting & details, which would then generate about 15 checkboxes as well as a button that contains code to run the report...kind of complicated. Does anyone know if this is even possible?

 

[dave3009]

Oh so these are on a worksheet...Perhaps try the OLEObjects approach

For i = 1 To 5
If Sheet1.OLEObjects("CheckBox" & i).Object.Value = True Then
'...Do something if the checkbox is checked
Else
'...Do something if the checkbox isn't checked
End If
Next i