Manipulating 150+ checkboxes with a macro in Excel 2000
 

Hi Tom -

Thanks for your prompt response. I'm having a problem where Excel
spits out an error box that says, simply, "400" in it, with an option
to click OK or Help. Help brings up the Help tool but with no
contents.

This code has been simplified a bit for my testing purposes:

Sub GlobalCheckbox_click()
Dim chbox As CheckBox
Dim chbox2 As CheckBox
Dim sName As String, num As String
sName = Application.Caller
Set chbox = ActiveSheet.CheckBoxes(sName)
If chbox.Value = xlOn Then
num = Right(sName, 3)
Set chbox2 = ActiveSheet.CheckBoxes("Checkbox" & CLng(num) + 64)
chbox2.Value = xlOn
End If
End Sub

The specific line it doesn't like is the "Set chbox2" line - I can't
seem to properly set the value of chbox2, even if I explicitly set it
with a line like:

Set chbox2 = ActiveSheet.CheckBoxes("Checkbox150")
or
Set chbox2 = ActiveSheet.CheckBoxes(sName)

It still gives me the "400" error.

The "chbox" variable sets correctly, and the "num" variable returns
the correct number.

Any ideas?

Thanks,
Stephan

"Tom Ogilvy" wrote in message ...
application.Caller will give you the name of the checkbox

Sub GlobalCheckbox_click()
dim chbox as Checkbox
dim chbox2 as Checkbox
dim sName as String, num as String
sName = application.Caller
set chbox = activesheet.Checkboxes(sName)
' next if block not needed if you only assign to checkboxes
' where the activity should happen
if chbox.TopLeftCell.Column = 1 then ' column A
if chbox.Value = xlOn then
' get number in name
if chbox.topLeftCell.row < 10 then
num = right(sname,1)
else
num = left(sName,1)
end if
set chbox2 = activesheet.Checkboxes("Checkbox" _
& clng(num)+64 )
chbox2.Value = xlon
end if
end if
End sub

make adjustments for the actual locations of your checkboxes in terms of
single digit values (less than 10) and column

Only assign the macro to checkboxes which should exhibit this behavior.
consider the code a pseudocode since it hasn't been tested and may contain
typos. Note that application.caller will not be defined if you try to run
the code manually using the run command or call from another routine.
--
Regards,
Tom Ogilvy

Manipulating 150+ checkboxes with a macro in Excel 2000
 

If you are working with checkboxes in the first column, are they all
numbered greater then 99

if not, then

num = Right(sName, 3)

would not have a number in it and

clng(num) would fail.

--
Regards,
Tom Ogilvy

"S. Romano" wrote in message
om...
Hi Tom -

Thanks for your prompt response. I'm having a problem where Excel
spits out an error box that says, simply, "400" in it, with an option
to click OK or Help. Help brings up the Help tool but with no
contents.

This code has been simplified a bit for my testing purposes:

Sub GlobalCheckbox_click()
Dim chbox As CheckBox
Dim chbox2 As CheckBox
Dim sName As String, num As String
sName = Application.Caller
Set chbox = ActiveSheet.CheckBoxes(sName)
If chbox.Value = xlOn Then
num = Right(sName, 3)
Set chbox2 = ActiveSheet.CheckBoxes("Checkbox" & CLng(num) + 64)
chbox2.Value = xlOn
End If
End Sub

The specific line it doesn't like is the "Set chbox2" line - I can't
seem to properly set the value of chbox2, even if I explicitly set it
with a line like:

Set chbox2 = ActiveSheet.CheckBoxes("Checkbox150")
or
Set chbox2 = ActiveSheet.CheckBoxes(sName)

It still gives me the "400" error.

The "chbox" variable sets correctly, and the "num" variable returns
the correct number.

Any ideas?

Thanks,
Stephan

"Tom Ogilvy" wrote in message
...
application.Caller will give you the name of the checkbox

Sub GlobalCheckbox_click()
dim chbox as Checkbox
dim chbox2 as Checkbox
dim sName as String, num as String
sName = application.Caller
set chbox = activesheet.Checkboxes(sName)
' next if block not needed if you only assign to checkboxes
' where the activity should happen
if chbox.TopLeftCell.Column = 1 then ' column A
if chbox.Value = xlOn then
' get number in name
if chbox.topLeftCell.row < 10 then
num = right(sname,1)
else
num = left(sName,1)
end if
set chbox2 = activesheet.Checkboxes("Checkbox" _
& clng(num)+64 )
chbox2.Value = xlon
end if
end if
End sub

make adjustments for the actual locations of your checkboxes in terms of
single digit values (less than 10) and column

Only assign the macro to checkboxes which should exhibit this behavior.
consider the code a pseudocode since it hasn't been tested and may
contain
typos. Note that application.caller will not be defined if you try to
run
the code manually using the run command or call from another routine.
--
Regards,
Tom Ogilvy

Manipulating 150+ checkboxes with a macro in Excel 2000
 

Sorry, Tom, should have been more specific. My checkboxes are actually
numbered 300+ for some interesting reason.

I dumped in a whole bunch of Msgbox commands to show me the results of
the variables as they are assigned, and they are all normal.

I'm fairly new to Excel macros but I've done some coding in VB before
and I've done all the testing I can to ensure that my syntax and
variables are all working correctly.

Further developments...

I popped the code into a module vs. putting it into the worksheet
code. The "400" error quit coming up, but now the same line of code is
rejected with:

Run-time error '1004':
Unable to get the CheckBoxes property of the Worksheet class

Is there a way to call the checkboxes property of the worksheet from
the module, instead of the worksheet itself? I tried
Worksheets("SheetName").CheckBoxes in case the ActiveSheet didn't work
for some reason, but that didn't work either.

Thanks for your continuing help with this.

Stephan

"Tom Ogilvy" wrote in message ...
If you are working with checkboxes in the first column, are they all
numbered greater then 99

if not, then

num = Right(sName, 3)

would not have a number in it and

clng(num) would fail.

--
Regards,
Tom Ogilvy