Importing Checkbox Values
 

Hello:

I have 20 checkboxes on a worksheet (called
PriorVersion.xls"), some of which may be checked, some
may not. I want to duplicate the checked checkboxes onto
another sheet (containing 20 identical checkboxes) using
code. Both sheets are called "Instructions", and are in
two separate workbooks.

I want to set up a For-Next loop to examine each checkbox
(1-20) in the original worksheet and make the counterpart
of each in the new workbook the same "Value" (True or
False, e.g. automatically check boxes 5, 8, 12 and 15 in
the new workbook if the same boxes are checked in the old
workbook).

The code below doesn't work because the syntax is wrong
(it doesn't like statement inside my For loop - "object
doesn't support this property or method").

Dim OLDINST, NEWINST as Object
Set OLDINST = Workbooks("PriorVersion.xls").Sheets
("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name).Sheets
("Instructions")
For BOX = 1 To 20
NEWINST.CheckboxBOX.Value = OLDINST.CheckboxBOX.Value
Next BOX

I could do it with 20 If-Then statements, but there has
to be a way to use the checkbox number as a variable.

One condition: the checkboxes in the new workbook call
macros when they are clicked. I DO NOT want this code to
execute when the values of the checkboxes are imported
from the previous workbook. I'm thinking this will be
the case (i.e., the code will not execute unless the
boxes are clicked, but I can change the values without
clicking the boxes).

Any ideas? Help is appreciated.
Thanks, MARTY

Importing Checkbox Values
 

For i = 1 To Worksheets("OLDINST").Checkboxes.Count
Worksheets("NEWINST").Checkboxes(i).Value = _
Worksheets("OLDINST").Checkboxes(i).Value
Next i



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marty" wrote in message
...
Hello:

I have 20 checkboxes on a worksheet (called
PriorVersion.xls"), some of which may be checked, some
may not. I want to duplicate the checked checkboxes onto
another sheet (containing 20 identical checkboxes) using
code. Both sheets are called "Instructions", and are in
two separate workbooks.

I want to set up a For-Next loop to examine each checkbox
(1-20) in the original worksheet and make the counterpart
of each in the new workbook the same "Value" (True or
False, e.g. automatically check boxes 5, 8, 12 and 15 in
the new workbook if the same boxes are checked in the old
workbook).

The code below doesn't work because the syntax is wrong
(it doesn't like statement inside my For loop - "object
doesn't support this property or method").

Dim OLDINST, NEWINST as Object
Set OLDINST = Workbooks("PriorVersion.xls").Sheets
("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name).Sheets
("Instructions")
For BOX = 1 To 20
NEWINST.CheckboxBOX.Value = OLDINST.CheckboxBOX.Value
Next BOX

I could do it with 20 If-Then statements, but there has
to be a way to use the checkbox number as a variable.

One condition: the checkboxes in the new workbook call
macros when they are clicked. I DO NOT want this code to
execute when the values of the checkboxes are imported
from the previous workbook. I'm thinking this will be
the case (i.e., the code will not execute unless the
boxes are clicked, but I can change the values without
clicking the boxes).

Any ideas? Help is appreciated.
Thanks, MARTY

Importing Checkbox Values
 

Dim OLDINST as worksheet, NEWINST as worksheet
Set OLDINST = Workbooks("PriorVersion.xls"). _
Sheets("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name). _
Sheets("Instructions")
For BOX = 1 To 20
NEWINST.OleObjects("Checkbox" & Box).Object.Value = _
OLDINST.OleObjects("Checkbox" & Box).Object.Value
Next BOX


This assumes the checkboxes are from the control toolbox toolbar. If from
the forms toolbar, the code would be different.

Also, I believe changing the value will trigger the event. If so, you would
need to have the event code check something and exit immediately if the
condition were met. You could then have the above code set the condition to
exit.

--
Regards,
Tom Ogilvy




"Marty" wrote in message
...
Hello:

I have 20 checkboxes on a worksheet (called
PriorVersion.xls"), some of which may be checked, some
may not. I want to duplicate the checked checkboxes onto
another sheet (containing 20 identical checkboxes) using
code. Both sheets are called "Instructions", and are in
two separate workbooks.

I want to set up a For-Next loop to examine each checkbox
(1-20) in the original worksheet and make the counterpart
of each in the new workbook the same "Value" (True or
False, e.g. automatically check boxes 5, 8, 12 and 15 in
the new workbook if the same boxes are checked in the old
workbook).

The code below doesn't work because the syntax is wrong
(it doesn't like statement inside my For loop - "object
doesn't support this property or method").

Dim OLDINST, NEWINST as Object
Set OLDINST = Workbooks("PriorVersion.xls").Sheets
("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name).Sheets
("Instructions")
For BOX = 1 To 20
NEWINST.CheckboxBOX.Value = OLDINST.CheckboxBOX.Value
Next BOX

I could do it with 20 If-Then statements, but there has
to be a way to use the checkbox number as a variable.

One condition: the checkboxes in the new workbook call
macros when they are clicked. I DO NOT want this code to
execute when the values of the checkboxes are imported
from the previous workbook. I'm thinking this will be
the case (i.e., the code will not execute unless the
boxes are clicked, but I can change the values without
clicking the boxes).

Any ideas? Help is appreciated.
Thanks, MARTY

Importing Checkbox Values
 

Worked like a charm. Thanks Tom!
I don't yet know if it activates the code for the
checkboxes. I'll work on that later, but for now, the
values match. Thanks again!

-----Original Message-----
Dim OLDINST as worksheet, NEWINST as worksheet
Set OLDINST = Workbooks("PriorVersion.xls"). _
Sheets("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name). _
Sheets("Instructions")
For BOX = 1 To 20
NEWINST.OleObjects("Checkbox" & Box).Object.Value = _
OLDINST.OleObjects("Checkbox" & Box).Object.Value
Next BOX


This assumes the checkboxes are from the control toolbox
toolbar. If from
the forms toolbar, the code would be different.

Also, I believe changing the value will trigger the
event. If so, you would
need to have the event code check something and exit
immediately if the
condition were met. You could then have the above code
set the condition to
exit.

--
Regards,
Tom Ogilvy




"Marty" wrote in
message
...
Hello:

I have 20 checkboxes on a worksheet (called
PriorVersion.xls"), some of which may be checked, some
may not. I want to duplicate the checked checkboxes
onto
another sheet (containing 20 identical checkboxes)
using
code. Both sheets are called "Instructions", and are
in
two separate workbooks.

I want to set up a For-Next loop to examine each
checkbox
(1-20) in the original worksheet and make the
counterpart
of each in the new workbook the same "Value" (True or
False, e.g. automatically check boxes 5, 8, 12 and 15
in
the new workbook if the same boxes are checked in the
old
workbook).

The code below doesn't work because the syntax is wrong
(it doesn't like statement inside my For loop - "object
doesn't support this property or method").

Dim OLDINST, NEWINST as Object
Set OLDINST = Workbooks("PriorVersion.xls").Sheets
("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name).Sheets
("Instructions")
For BOX = 1 To 20
NEWINST.CheckboxBOX.Value = OLDINST.CheckboxBOX.Value
Next BOX

I could do it with 20 If-Then statements, but there has
to be a way to use the checkbox number as a variable.

One condition: the checkboxes in the new workbook call
macros when they are clicked. I DO NOT want this code
to
execute when the values of the checkboxes are imported
from the previous workbook. I'm thinking this will be
the case (i.e., the code will not execute unless the
boxes are clicked, but I can change the values without
clicking the boxes).

Any ideas? Help is appreciated.
Thanks, MARTY


.

Importing Checkbox Values
 

This one works as well.

Thanks, Bob!

-----Original Message-----
For i = 1 To Worksheets("OLDINST").Checkboxes.Count
Worksheets("NEWINST").Checkboxes(i).Value = _
Worksheets("OLDINST").Checkboxes(i).Value
Next i



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Marty" wrote in
message
...
Hello:

I have 20 checkboxes on a worksheet (called
PriorVersion.xls"), some of which may be checked, some
may not. I want to duplicate the checked checkboxes
onto
another sheet (containing 20 identical checkboxes)
using
code. Both sheets are called "Instructions", and are
in
two separate workbooks.

I want to set up a For-Next loop to examine each
checkbox
(1-20) in the original worksheet and make the
counterpart
of each in the new workbook the same "Value" (True or
False, e.g. automatically check boxes 5, 8, 12 and 15
in
the new workbook if the same boxes are checked in the
old
workbook).

The code below doesn't work because the syntax is wrong
(it doesn't like statement inside my For loop - "object
doesn't support this property or method").

Dim OLDINST, NEWINST as Object
Set OLDINST = Workbooks("PriorVersion.xls").Sheets
("Instructions")
Set NEWINST = Workbooks(ActiveWorkbook.Name).Sheets
("Instructions")
For BOX = 1 To 20
NEWINST.CheckboxBOX.Value = OLDINST.CheckboxBOX.Value
Next BOX

I could do it with 20 If-Then statements, but there has
to be a way to use the checkbox number as a variable.

One condition: the checkboxes in the new workbook call
macros when they are clicked. I DO NOT want this code
to
execute when the values of the checkboxes are imported
from the previous workbook. I'm thinking this will be
the case (i.e., the code will not execute unless the
boxes are clicked, but I can change the values without
clicking the boxes).

Any ideas? Help is appreciated.
Thanks, MARTY


.