string
 

In the following code, I'm attempting to make all my checkboxes clear t
a .false state, using a for next loop, Instead of typing each box in.

I'm new to vb and am unsure how I use a string variable with th
checkbox objects. Have a look and see if you can help.

Thanks Dennis

Private Sub Cm1_Click()

Dim x As String

For c = 1 To 55
'Dim x As String
x = "a" + c
x.Value = False
next c
'instead of below
'a1.Value = False
'a2.Value = False
'a3, a4, a5,...
't1.Text = ""

End Su

--
terrysoper197
-----------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=49892

string
 

Are these checkboxes in a userform or checkboxes on a worksheet?

If they're on a worksheet, are they from the Forms toolbar or from the Control
toolbox toolbar.

On a Userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.CheckBox Then
ctrl.Object.Value = False
End If
Next ctrl
End Sub

On a worksheet from the Forms toolbar:

Option Explicit
Sub testme()
Worksheets("Sheet1").CheckBoxes.Value = False
End Sub

One a worksheet from the Control Toolbox Toolbar:

Option Explicit
Private Sub CommandButton1_Click()
Dim OLEObj As OLEObject
For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is msforms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub

The me. keyword refers to the thing that owns the code--either the userform or
the worksheet with the button and checkboxes.


terrysoper1973 wrote:

In the following code, I'm attempting to make all my checkboxes clear to
a .false state, using a for next loop, Instead of typing each box in.

I'm new to vb and am unsure how I use a string variable with the
checkbox objects. Have a look and see if you can help.

Thanks Dennis

Private Sub Cm1_Click()

Dim x As String

For c = 1 To 55
'Dim x As String
x = "a" + c
x.Value = False
next c
'instead of below
'a1.Value = False
'a2.Value = False
'a3, a4, a5,...
't1.Text = ""

End Sub

--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

--

Dave Peterson

string
 

The checkboxes are in a worksheet, not sure about the other.

I've uploaded a sample for you.


+-------------------------------------------------------------------+
|Filename: fool balance.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4191 |
+-------------------------------------------------------------------+

--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

string
 

I don't use excelforum so I can't see the attachment.

But I wouldn't open an attachment anyway. (Lots of people won't.)

Better to post in plain text.

terrysoper1973 wrote:

The checkboxes are in a worksheet, not sure about the other.

I've uploaded a sample for you.

+-------------------------------------------------------------------+
|Filename: fool balance.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4191 |
+-------------------------------------------------------------------+

--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

--

Dave Peterson

string
 

To clear all the checkboxes can be done by putting the statment
a1.value = false for each checkbox, however, there would be a lot of
code if I need 100-200 checkboxes; so I'm asking if it is possible set
up a for next loop that replaces the a1.value = false, a2.value =
false, a3.value = false... with a simple sting that replaces the a#
part of the object with an
a(string).value = false, but I'm not sure about the syntax, or if it is
even possible.

Please help, and sorry about the file upload, we all have to be careful
about what we open, won't happen again.

The code below is used to simulate my excel sheet.


Private Sub a1_Click()
'first check box test
Worksheets("sheet1").t1.Text = "fool"

End Sub

Private Sub a2_Click()
'second check box in sheet
Worksheets("sheet1").t1.Text = "fool2"
End Sub

Private Sub Cm1_Click()
'command button to make all checkboxes false

'works until x.value = false line, stops at the x
'For c = 1 To 2
'Dim x As String
'x = "a" + c
'x.Value = False
'next c

'works
'a2.Value = False
'a3, a4, a5,...
't1.Text = ""

End Sub

Private Sub Worksheet_Activate()
'works

a1.Value = False
a2.Value = False

End Sub


--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

string
 

You can do something like the following:

Dim Ndx As Long
For Ndx = 1 To 2
UserForm1.Controls("A" & Ndx).Value = False
Next Ndx

Or, to clear all checkboxes,

Dim Ctrl As MSForms.Control
For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
Ctrl.Value = False
End If
Next Ctrl

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"terrysoper1973"
<terrysoper1973.21a5pm_1136663701.3341@excelforu m-nospam.com
wrote in message
news:terrysoper1973.21a5pm_1136663701.3341@excelfo rum-nospam.com...

To clear all the checkboxes can be done by putting the statment
a1.value = false for each checkbox, however, there would be a
lot of
code if I need 100-200 checkboxes; so I'm asking if it is
possible set
up a for next loop that replaces the a1.value = false, a2.value
=
false, a3.value = false... with a simple sting that replaces
the a#
part of the object with an
a(string).value = false, but I'm not sure about the syntax, or
if it is
even possible.

Please help, and sorry about the file upload, we all have to be
careful
about what we open, won't happen again.

The code below is used to simulate my excel sheet.


Private Sub a1_Click()
'first check box test
Worksheets("sheet1").t1.Text = "fool"

End Sub

Private Sub a2_Click()
'second check box in sheet
Worksheets("sheet1").t1.Text = "fool2"
End Sub

Private Sub Cm1_Click()
'command button to make all checkboxes false

'works until x.value = false line, stops at the x
'For c = 1 To 2
'Dim x As String
'x = "a" + c
'x.Value = False
'next c

'works
'a2.Value = False
'a3, a4, a5,...
't1.Text = ""

End Sub

Private Sub Worksheet_Activate()
'works

a1.Value = False
a2.Value = False

End Sub


--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile:
http://www.excelforum.com/member.php...o&userid=30161
View this thread:
http://www.excelforum.com/showthread...hreadid=498925

string
 

Dim Ndx As Long
For Ndx = 1 To 2
UserForm1.Controls("A" & Ndx).Value = False
Next Ndx

My checkboxes are in the workbook and not on a form. I'm just not
understanding the code.

I tried to type the code in but got an error message "type missmatch"

So I tried the following
Dim Ndx As Long
For Ndx = 1 To 2
worksheets("sheet1").Controls("A" & Ndx).Value = False
Next Ndx

Well maybe I get marks for creativity
I may be in over my head. So if you think your waisting your time, I
won't be offended, but I do welcome your help.

Thanks
Dennis


--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

string
 

Did you use the checkbox from the control toolbox toolbar or did you use the
checkboxes from the Forms toolbar?

terrysoper1973 wrote:

Dim Ndx As Long
For Ndx = 1 To 2
UserForm1.Controls("A" & Ndx).Value = False
Next Ndx

My checkboxes are in the workbook and not on a form. I'm just not
understanding the code.

I tried to type the code in but got an error message "type missmatch"

So I tried the following
Dim Ndx As Long
For Ndx = 1 To 2
worksheets("sheet1").Controls("A" & Ndx).Value = False
Next Ndx

Well maybe I get marks for creativity
I may be in over my head. So if you think your waisting your time, I
won't be offended, but I do welcome your help.

Thanks
Dennis

--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

--

Dave Peterson

string
 

for each obj in ActiveSheet.OleObjects
if typeof Obj.Object is MSForms.CheckBox then
obj.Object.Value = False
end if
Next

or
if the names of the checkboxes are of the form A# then

for i = 1 to 10
Activesheet.OleObjects("A" & i).Object.Value = False
Next

The above is for checkboxes from the control toolbox toolbar. If from the
forms toolbar


for each cbox in Activesheet.CheckBoxes
cbox.Value = xlOff
Next


or

for i = 1 to 10
activesheet.Checkboxes("A" & i).Value = xlOff
Next

or if only 10 checkboxes

for i = 1 to 10
activesheet.Checkboxes(i).Value = xloff
Next

--
Regards,
Tom Ogilvy


"terrysoper1973"
<terrysoper1973.21ahza_1136679602.1953@excelforu m-nospam.com wrote in
message news:terrysoper1973.21ahza_1136679602.1953@excelfo rum-nospam.com...

Dim Ndx As Long
For Ndx = 1 To 2
UserForm1.Controls("A" & Ndx).Value = False
Next Ndx

My checkboxes are in the workbook and not on a form. I'm just not
understanding the code.

I tried to type the code in but got an error message "type missmatch"

So I tried the following
Dim Ndx As Long
For Ndx = 1 To 2
worksheets("sheet1").Controls("A" & Ndx).Value = False
Next Ndx

Well maybe I get marks for creativity
I may be in over my head. So if you think your waisting your time, I
won't be offended, but I do welcome your help.

Thanks
Dennis


--
terrysoper1973
------------------------------------------------------------------------
terrysoper1973's Profile:
http://www.excelforum.com/member.php...o&userid=30161
View this thread: http://www.excelforum.com/showthread...hreadid=498925

string
 

Thanks for your help everyone. Your all in my prayers.

Thanks
Denni

--
terrysoper197
-----------------------------------------------------------------------
terrysoper1973's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=49892