Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question on VBA and Checkboxes - Newbie | Excel Programming | |||
question regaridng checkboxes | Excel Programming | |||
Real Newbie newbie question | New Users to Excel | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Newbie question | Excel Programming |