Do I need VBA to do this?

[TheRobster]

Hi all,

I'm trying to create an Excel spreadsheet that can create a compiled list from an existing list. By this I mean there is a list of items which represents the complete list and each entry has a checkbox next to it to indicate whether it has been selected or not, and then on the same worksheet there is another list which only contains the items that have been selected from the first, full list.

Check out these screensheets to get a better idea of what I mean:

http://homepages.nildram.co.uk/~proebuck/excel1.JPG

http://homepages.nildram.co.uk/~proebuck/excel2.JPG

I think this should be possible using some kind of database range/filter/criteria type functions. I've tried using a pivot table but the problems are that this doesn't update everytime you select/deselect another item (it has to do this) and it seems to be an over-complicated solution for what is essentially a simple task......or it seems simple but I can't get it to work! Do I need a VBA routine to make this work or what?

Also, the items in the compiled list have to appear in the same order as the original list. I'm also not sure if just hiding a row would work as Excel still counts the cells as being there e.g. if you copy/paste the cell range then any hidden cells are also copied and I don't want this to happen.

Any ideas?

Cheers
-Rob

[salvelinus]

I don't know as much about Excel, but this brings up one of my pet peeves. This type of project is much better suited for Access or another db, and using SQL. Maybe that's not an option for you, but it would be a trivial matter in a real database.
I know Excel has minimal db capabilities, but, IMHO, Excel should be used for calculations. Data storage (filtering, sorting, relating, etc) is a database task. It's not that hard to do a task in one and export the results to the other, if necessary.
You can use pliers to pound in nails, but if you have a hammer, why not use that?

[TheRobster]

Using a DB to do it would be overcomplicating things........99% of the application would still be in Excel so to have Access to do this one tiny part of it would be a bit of a pain. Really I want to keep all the functions of my application within Excel.

I would have thought the type of thing I want to do would be possible using list ranges or something..........but I dunno.

[RobDog888]

Looks like to me all you need is a macro in VBA to iterate through
the checkboxes. If they are selected then add to list. Have your
original list already sorted so when the user selects then they will
already be sorted.

One procedure, no database.

'pseudocode
Sub Selection_Change()
If checkbox1.vlaue = true then
    Sheet2.Cells(1,1) = "Dog" 'or ???
endif
 
if checkbox2.value = true then
    sheet2.cells(2,1) = "Monkey"
endif
'...
'...
'...
End Sub

[TheRobster]

Would I need to call the subroutine everytime I select/deselect another item, or would it run and update automatically if I have it running in the background of that particular worksheet?

Sorry for the n00b questions but I'm new to VBA and I'm still getting my head round most of the basics.

Cheers
-Rob

[RobDog888]

If you use the Selection_Change event behind the sheet then it
will run whenever you change the cell that has the focus. So as
you check or uncheck the checkboxes it will run once for each
selection/deselection.

HTH

[TheRobster]

Originally posted by RobDog888
If you use the Selection_Change event behind the sheet then it
will run whenever you change the cell that has the focus. So as
you check or uncheck the checkboxes it will run once for each
selection/deselection.

HTH

Okay, thanks for the help.

One final thing..........I take it the checkbox has to be an ActiveX checkbox and not a Forms checkbox? For some reason programming VBA code for Forms items never seems to work with anything more complicated than just selecting cells or pasting cells....the ActiveX controls seem to be more focused towards running VBA code.

[RobDog888]

Yes, but do you really need the Forms checkbox?

[TheRobster]

Originally posted by RobDog888
Yes, but do you really need the Forms checkbox?

Not really, but they are the objects that I used when I first started using Excel, mainly because they are easier to configure. (You just right-click and alter the properties using the dialog box rather than having to program any VBA code).