Variable arrays & checkboxes

[Ray]

Thanks to Bernie, I was able to build a macro to consolidate data
submitted by my stores. Currently, the macro opens approx 30
workbooks (one at a time), copies the specified data, and pastes it
into the proper column in the summary workbook. It's really going to
be a time-saver....

The next step .... sometimes, a store re-submits their data after the
consolidation has been completed. Instead of having to run the whole
consolidation again, I'd like to be able to specify which files need
to be updated.

So, here's my idea -- the current macro utilizes a 'hard-coded' array
to determine which files to open. I'd like to use checkboxes to
identify them instead. I'd have a list of the stores with a checkbox
next to each, plus an extra checkbox to 'update all'.

I know how to make the checkboxes and name them, but this is where I
get lost -- how do I build a macro to capture the names of checked
boxes and put them into an array? And how would I code the macro to
see the 'update all' box and know what to do?

TIA,
ray

 

[Dave Peterson]

It might be easier (although not as pretty) to use a worksheet.

Put the filenames in column B (say B1:B30)
And put an X in A1:A30 if the file named in column B should be processed.

Then you could use:

for each mycell in worksheets("sheet999").range("b1:b30")
if lcase(mycell.offset(0,-1).value = lcase("x") then
'do the work
'your code goes here
else
'do nothing
end if
next mycell

 

[Ray]

Dave -

Would the same logic apply is I used the checkboxes and had them
linked to a cell (say A1:A30)? Instead of using another worksheet...?

thanks for your help today!
br//ray

 

[Dave Peterson]

The same logic, but you wouldn't check for "x".

if mycell.offset(0,-1).value = true then
'it's checked
else
'not checked
end if