Newbie : question on VBA and checkboxes
 

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

Newbie : question on VBA and checkboxes
 

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" wrote:

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

Newbie : question on VBA and checkboxes
 

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


"OssieMac" a écrit dans le message de
news: ...
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" wrote:

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

Newbie : question on VBA and checkboxes
 

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

"Daniel" a écrit dans le message de news:
...
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


"OssieMac" a écrit dans le message de
news: ...
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" wrote:

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

Newbie : question on VBA and checkboxes
 

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

Newbie : question on VBA and checkboxes
 

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


"OssieMac" a écrit dans le message de
news: ...
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

Newbie : question on VBA and checkboxes
 

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


"OssieMac" a écrit dans le message de
news: ...
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