Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
I have the line of code
Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
On Jan 6, 8:59*am, jkrons wrote:
I have the line of code Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes? I don't know of any way to do this directly. If you keep the default control names (or consistently adopt a naming convention) you could use the like operator: Sub test() Dim control As OLEObject For Each control In Sheets(1).OLEObjects If control.Name Like "CheckBox*" Then MsgBox control.Name Next End Sub hth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
On 6 Jan., 16:47, John Coleman wrote:
On Jan 6, 8:59*am, jkrons wrote: I have the line of code Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes? I don't know of any way to do this directly. If you keep the default control names (or consistently adopt a naming convention) you could use the like operator: Sub test() * * Dim control As OLEObject * * For Each control In Sheets(1).OLEObjects * * * * If control.Name Like "CheckBox*" Then MsgBox control.Name * * Next End Sub hth Thank you. That helped. Jan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
For Each mychkbox In wks.CheckBoxes
With mychkbox .Value = True End With Next mychkbox Gord On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote: I have the line of code Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
On Jan 6, 11:36*am, Gord Dibben wrote:
For Each mychkbox In wks.CheckBoxes * * * * With mychkbox * * * * * .Value = True * * * * End With * * Next mychkbox Gord That seems to work for form controls but not ActiveX controls. It also seems to be poorly documented (e.g. doesn't appear in the object browser for worksheet methods) so thanks for pointing it out. On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote: I have the line of code Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through checkboxes
You are correct John.
Forms only............was not paying attention(usual state). Did not notice OP required code on Activex Checkboxes. The code I posted was part of a larger set by Dave Peterson for changing Forms checkbox linked cells. Gord On Fri, 6 Jan 2012 09:09:37 -0800 (PST), John Coleman wrote: On Jan 6, 11:36*am, Gord Dibben wrote: For Each mychkbox In wks.CheckBoxes * * * * With mychkbox * * * * * .Value = True * * * * End With * * Next mychkbox Gord That seems to work for form controls but not ActiveX controls. It also seems to be poorly documented (e.g. doesn't appear in the object browser for worksheet methods) so thanks for pointing it out. On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote: I have the line of code Sheets(1).OLEObjects("CheckBox1").Object.Value = True that works great, but how do I loop through all the checkboxes in my sheet with something like this For Each cb In Sheets(1).OLEObjects.CheckBoxes Sheets(1).OLEObjects(cb.Name).Object.Value = True I get a runtime error 430 Object doesn't support this property or method. How do I loop through the checkboxes?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through checkboxes | Excel Programming | |||
Looping Checkboxes in Worksheet | Excel Programming | |||
Looping through a group of checkboxes | Excel Programming | |||
Looping Checkboxes on Worksheet | Excel Programming | |||
Checkboxes to spreadsheet using a looping macro | Excel Programming |