Newbie : question on VBA and checkboxes

[Daniel]

Hi everybody.

I have this worksheet which contains one checkbox on each line of a specific
column, let's say Checkbox1 to checkbox20.
I can determine whether the checkboxes are checked or not using instructions
such as :

If ActiveSheet.CheckBoxes1.Value = True Then [Process]


However I would like to use a For / Next routine to test all the checkboxes.

I entered the following code, but it doesn't work and generates an error
message :

For i = 1 To n 'n = number of lines
If ActiveSheet.CheckBoxes(i).Value = True Then
[Process]
End If
Next i

The error message indicates :
Error 1004.
"Impossible to read the Checkbox property of the worksheet class " (sorry,
it's a lousy translation of the French version of Excel).

What did I do wrong ? What should I do ?

I am using Excel 2002

again, many tks in advance for your time and kind help.
Brgds,
Daniel

 

[OssieMac]

Hi Daniel,

The following works if you only have check boxes (or you could test the name
of the object) but I am interested if anyone can tell me how to determine if
the object is a checkbox because it picks up all objects.

Sub macro2()
Dim oleChk As OLEObject

For Each oleChk In ActiveSheet.OLEObjects
If oleChk.Object.Value = True Then
MsgBox oleChk.Name & " = " & oleChk.Object.Value
Else
MsgBox oleChk.Name & " = " & oleChk.Object.Value
End If
Next oleChk

End Sub

Regards,

Ossiemac

 

[Daniel]

Hi OssieMac.

Many thanks for your help.
I tried your code and it works ! It checks the Checkboxes in the same order
as they where created in the worksheet.

I didn't expect to have to write such a routine to check the boxes. Could
you recommend an Internet site where everything I should know about VBA and
checkboxes / objects is described ?

again, thank you very much for your time.

With best regards,
Daniel

Hi Daniel,

The following works if you only have check boxes (or you could test the
name
of the object) but I am interested if anyone can tell me how to determine
if
the object is a checkbox because it picks up all objects.

Sub macro2()
Dim oleChk As OLEObject

For Each oleChk In ActiveSheet.OLEObjects
If oleChk.Object.Value = True Then
MsgBox oleChk.Name & " = " & oleChk.Object.Value
Else
MsgBox oleChk.Name & " = " & oleChk.Object.Value
End If
Next oleChk

End Sub

Regards,

Ossiemac

Hi everybody.

I have this worksheet which contains one checkbox on each line of a
specific
column, let's say Checkbox1 to checkbox20.
I can determine whether the checkboxes are checked or not using
instructions
such as :

If ActiveSheet.CheckBoxes1.Value = True Then [Process]


However I would like to use a For / Next routine to test all the
checkboxes.

I entered the following code, but it doesn't work and generates an error
message :

For i = 1 To n 'n = number of lines
If ActiveSheet.CheckBoxes(i).Value = True Then
[Process]
End If
Next i

The error message indicates :
Error 1004.
"Impossible to read the Checkbox property of the worksheet class "
(sorry,
it's a lousy translation of the French version of Excel).

What did I do wrong ? What should I do ?

I am using Excel 2002

again, many tks in advance for your time and kind help.
Brgds,
Daniel

 

[Daniel]

Hello again, OssieMac,

Sorry to bother you. Could you suggest a code which would allaox me to test
any checkbox individually, by means of an index ?
e.g. something like :
For i= 1 to n
If ActiveSheet.CheckBoxes(i).Value = True Then...

Many thanks,
Brgds,
Daniel

Hi OssieMac.

Many thanks for your help.
I tried your code and it works ! It checks the Checkboxes in the same
order as they where created in the worksheet.

I didn't expect to have to write such a routine to check the boxes. Could
you recommend an Internet site where everything I should know about VBA
and checkboxes / objects is described ?

again, thank you very much for your time.

With best regards,
Daniel

Hi Daniel,

The following works if you only have check boxes (or you could test the
name
of the object) but I am interested if anyone can tell me how to determine
if
the object is a checkbox because it picks up all objects.

Sub macro2()
Dim oleChk As OLEObject

For Each oleChk In ActiveSheet.OLEObjects
If oleChk.Object.Value = True Then
MsgBox oleChk.Name & " = " & oleChk.Object.Value
Else
MsgBox oleChk.Name & " = " & oleChk.Object.Value
End If
Next oleChk

End Sub

Regards,

Ossiemac

Hi everybody.

I have this worksheet which contains one checkbox on each line of a
specific
column, let's say Checkbox1 to checkbox20.
I can determine whether the checkboxes are checked or not using
instructions
such as :

If ActiveSheet.CheckBoxes1.Value = True Then [Process]


However I would like to use a For / Next routine to test all the
checkboxes.

I entered the following code, but it doesn't work and generates an error
message :

For i = 1 To n 'n = number of lines
If ActiveSheet.CheckBoxes(i).Value = True Then
[Process]
End If
Next i

The error message indicates :
Error 1004.
"Impossible to read the Checkbox property of the worksheet class "
(sorry,
it's a lousy translation of the French version of Excel).

What did I do wrong ? What should I do ?

I am using Excel 2002

again, many tks in advance for your time and kind help.
Brgds,
Daniel

 

[OssieMac]

Hi Daniel,

Couple of sites to check out for information. Don't really know if you will
find what you want on check boxes but I have found them good for other stuff:-

http://www.cpearson.com/Excel/Search.htm

http://www.mrexcel.com/

In answer to your other question: Here are two sample code examples which
might help:-

Note: I don't know why Object is required after OLEObjects to obtain the
value when it is not required for the Name. (I adapted info from a reply to
me from Chip Pearson on another issue with Combo boxes.)


Option 1:
Sub Macro1()
Dim i As Integer

For i = 1 To ActiveSheet.OLEObjects.Count
If ActiveSheet.OLEObjects(i).Object = True Then
MsgBox ActiveSheet.OLEObjects(i).Name & Chr(13) & _
ActiveSheet.OLEObjects(i).Object.Value
Else
MsgBox ActiveSheet.OLEObjects(i).Name & Chr(13) & _
ActiveSheet.OLEObjects(i).Object.Value
End If
Next i

End Sub



Option 2:
Sub Macro2()
Dim i As Integer

With ActiveSheet
For i = 1 To .OLEObjects.Count
If .OLEObjects(i).Object = True Then
MsgBox .OLEObjects(i).Name & Chr(13) & _
.OLEObjects(i).Object.Value
Else
MsgBox .OLEObjects(i).Name & Chr(13) & _
.OLEObjects(i).Object.Value
End If
Next i
End With

End Sub


Regards,

OssieMac

 

[Daniel]

thank you very much, OssieMac, for your time and patience. I am most
grateful.
will let u know about the results I get.
have a nice day.
best regards,
Daniel

 

[Daniel]

Hi OssieMac,

Just to let u know that your macro option 1 works fine. For some reason the
option 2 doesn't. Anyway, I can move ahead with my project, now.

Thank u very much again.
with brgds,
Daniel