Importing Checkbox Values

M

Marty

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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.
 
M

MARTY

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!
 
M

MARTY

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)




.
Click to expand...
 
Top