I am not able to access check boxes through macros. I searched the help
files and this forum, and I know that I am supposed to use something like
"CheckBox1", but it keeps telling me that "Object Required."

Here's my code:

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
mycheckboxes = (Array("checkbox1", "check box 5", "check box 6", "check box
7"))

Dim cost As Integer
cost = 0

For Each box In mycheckboxes
If box.Value = Checked Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

Thanks for the advice.

Hi,Pflugs

Sub CheckBox1_Click()
Dim mycheckboxes As Variant
mycheckboxes = Array("CheckBox1", "Checkbox5", "Checkbox6", "Checkbox7")
Dim cost As Integer
cost = 0
For Each box In Me.Controls
If TypeName(box) = "CheckBox" Then
On Error Resume Next
r = Application.WorksheetFunction.Match(box.Name, mycheckboxes, 0)
If Err = 0 And box Then
cost = cost + 1
End If
End If
Next
Range("j7").Value = cost
End Sub

--
http://www.vba.com.tw/plog/


"Pflugs" wrote:

I am not able to access check boxes through macros. I searched the help
files and this forum, and I know that I am supposed to use something like
"CheckBox1", but it keeps telling me that "Object Required."

Here's my code:

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
mycheckboxes = (Array("checkbox1", "check box 5", "check box 6", "check box
7"))

Dim cost As Integer
cost = 0

For Each box In mycheckboxes
If box.Value = Checked Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

Thanks for the advice.

Hello Pflugs,

The problem is there are 2 types of Check Boxes in Excel and both ar
accessible through VBA. They are the Forms Toolbar Controls and th
Control Toolbox Controls.

The Forms Toolbar Controls are available from Excel by checking "Forms
under Toolbars. Control Toolbox Controls belong to VBA and are accesse
from Excel by typing ALT + F11 to bring up the Visual Basic Editor o
by selecting the VBA Toolbar under Toolbars.

Your code sample is written for Control Toolbox Controls not Form
Toolbar Controls. Forms Toolbox check boxes are flat, and Contro
ToolBox check boxes are 3D. The name 'checkbox1" is a VBA referenc
name while "Check Box 5" is clearly a Forms Toolbar reference.

Unless I know which type of check box you are using, correcting th
code would difficult, especially if you have mixed the 2 types of chec
boxes.

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48000

Looks like you have checkboxes form the forms toolbar. I will assume the
names in your array are correct. It you get an error, that is the first
place to look.

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
Dim box
mycheckboxes = Array("checkbox1", "check box 5", _
"check box 6", "check box 7")

Dim cost As Long
cost = 0

For Each box In mycheckboxes
If Activesheet.CheckBoxes(box).Value = xlOn Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

--
Regards,
Tom Ogilvy

"Pflugs" wrote in message
...
I am not able to access check boxes through macros. I searched the help
files and this forum, and I know that I am supposed to use something like
"CheckBox1", but it keeps telling me that "Object Required."

Here's my code:

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
mycheckboxes = (Array("checkbox1", "check box 5", "check box 6", "check
box
7"))

Dim cost As Integer
cost = 0

For Each box In mycheckboxes
If box.Value = Checked Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

Thanks for the advice.

Wow, I guess that must have been my mistake. I didn't know there were two
types of check boxes. Tom, the code you sent me worked perfectly. This will
enable me to set auto-updating tally sheets for my friend's business.

Am I able to make arrays of all buttons and shapes (i.e. radio buttons) and
perform this same type of check on them, too? How do I know what name to
call them (i.e. CheckBoxes, OptionButtons, others are...)?

Thanks to all,

Pflugs

"Tom Ogilvy" wrote:

Looks like you have checkboxes form the forms toolbar. I will assume the
names in your array are correct. It you get an error, that is the first
place to look.

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
Dim box
mycheckboxes = Array("checkbox1", "check box 5", _
"check box 6", "check box 7")

Dim cost As Long
cost = 0

For Each box In mycheckboxes
If Activesheet.CheckBoxes(box).Value = xlOn Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

--
Regards,
Tom Ogilvy

"Pflugs" wrote in message
...
I am not able to access check boxes through macros. I searched the help
files and this forum, and I know that I am supposed to use something like
"CheckBox1", but it keeps telling me that "Object Required."

Here's my code:

Sub CheckBox1_Click()

Dim mycheckboxes As Variant
mycheckboxes = (Array("checkbox1", "check box 5", "check box 6", "check
box
7"))

Dim cost As Integer
cost = 0

For Each box In mycheckboxes
If box.Value = Checked Then
cost = cost + 1
End If
Next

Range("j7").Value = cost

End Sub

Thanks for the advice.