question on VBA and Checkboxes - Newbie

D

Daniel

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
 
B

Bob Phillips

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)
 
D

Daniel

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
 
D

Daniel

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

Mike Fogleman

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top