question on VBA and Checkboxes - Newbie
 

Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and 20
columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes, checked
or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions look
like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I have
to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie

question on VBA and Checkboxes - Newbie
 

One of two ways

For Each chb In ActiveSheet.CheckBoxes
If chb.Value = 1 Then MsgBox chb.name & " = checked"
Next chb


although this will go through them in the order they were createed, OR

For i = 1 To 20
If ActiveSheet.CheckBoxes("Check Box " & i).Value = 1 Then _
MsgBox ActiveSheet.CheckBoxes("Check Box " & i).name & " =
checked"
Next i

as long as they all have a structured name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Daniel" wrote in message
...
Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and 20
columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes,
checked or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions
look like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I have
to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie

question on VBA and Checkboxes - Newbie
 

Hi Bob !

thank you so much for your time and precious help.
I really do appreciate it.
It's all clear.
very best regards,
Daniel


"Bob Phillips" a écrit dans le message de news:
...
One of two ways

For Each chb In ActiveSheet.CheckBoxes
If chb.Value = 1 Then MsgBox chb.name & " = checked"
Next chb


although this will go through them in the order they were createed, OR

For i = 1 To 20
If ActiveSheet.CheckBoxes("Check Box " & i).Value = 1 Then _
MsgBox ActiveSheet.CheckBoxes("Check Box " & i).name & " =
checked"
Next i

as long as they all have a structured name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Daniel" wrote in message
...
Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and 20
columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes,
checked or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions
look like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I
have to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie

question on VBA and Checkboxes - Newbie
 

Sorry, Bob, what do u mean by "as long as they have a structured name" ?
Brgds,
Daniel


"Bob Phillips" a écrit dans le message de news:
...
One of two ways

For Each chb In ActiveSheet.CheckBoxes
If chb.Value = 1 Then MsgBox chb.name & " = checked"
Next chb


although this will go through them in the order they were createed, OR

For i = 1 To 20
If ActiveSheet.CheckBoxes("Check Box " & i).Value = 1 Then _
MsgBox ActiveSheet.CheckBoxes("Check Box " & i).name & " =
checked"
Next i

as long as they all have a structured name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Daniel" wrote in message
...
Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and 20
columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes,
checked or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions
look like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I
have to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie

question on VBA and Checkboxes - Newbie
 

Bob means that the Checkboxes have a structured number pattern in their
names.
CheckBox1
CheckBox2
CheckBox3
etc

For i = 1 to 20 means we are going to loop through CheckBox names that end
in 1 thru 20 and ask them if they are "Checked" . If the Checkboxes are not
named in this structured way then we will fail in the macro.

Mike F
"Daniel" wrote in message
...
Sorry, Bob, what do u mean by "as long as they have a structured name" ?
Brgds,
Daniel


"Bob Phillips" a écrit dans le message de news:
...
One of two ways

For Each chb In ActiveSheet.CheckBoxes
If chb.Value = 1 Then MsgBox chb.name & " = checked"
Next chb


although this will go through them in the order they were createed, OR

For i = 1 To 20
If ActiveSheet.CheckBoxes("Check Box " & i).Value = 1 Then _
MsgBox ActiveSheet.CheckBoxes("Check Box " & i).name & " =
checked"
Next i

as long as they all have a structured name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Daniel" wrote in message
...
Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and
20 columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes,
checked or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions
look like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I
have to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie

question on VBA and Checkboxes - Newbie
 

Thanks a lot, Mike.
with my best regards,
Daniel


"Mike Fogleman" a écrit dans le message de
news: ...
Bob means that the Checkboxes have a structured number pattern in their
names.
CheckBox1
CheckBox2
CheckBox3
etc

For i = 1 to 20 means we are going to loop through CheckBox names that end
in 1 thru 20 and ask them if they are "Checked" . If the Checkboxes are
not named in this structured way then we will fail in the macro.

Mike F
"Daniel" wrote in message
...
Sorry, Bob, what do u mean by "as long as they have a structured name" ?
Brgds,
Daniel


"Bob Phillips" a écrit dans le message de news:
...
One of two ways

For Each chb In ActiveSheet.CheckBoxes
If chb.Value = 1 Then MsgBox chb.name & " = checked"
Next chb


although this will go through them in the order they were createed, OR

For i = 1 To 20
If ActiveSheet.CheckBoxes("Check Box " & i).Value = 1 Then _
MsgBox ActiveSheet.CheckBoxes("Check Box " & i).name & " =
checked"
Next i

as long as they all have a structured name.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Daniel" wrote in message
...
Hi everybody,

I have this Excel worksheet which, to make it simple, has 10 lines and
20 columns.

I inserted a checkboxIn each cell of column 19 and column 20.

And now I would like to determine the value of all this checkboxes,
checked or not checked, by means of a VBA code.

Could someone put me on the right track ? What would the instructions
look like, to "scan" the 2 columns and return the value of each box ?

Do I have to declare all these checkboxes ? If so, how and where do I
have to do this in the sub ?


Many thanks in advance for your kind help.
With best regards,

Daniel
VBA newbie