Using checkboxes to add/remove values to a table

Re: Using checkboxes to add/remove values to a table

Tybaltcap,

I remember what being a novice is like with Excel VBA. What you are trying to do is not overly complicated but daunting if your new to VBA.

There are a number of ways this could be tackled and with the limited information you supplied a number of assumptions would need to made about the data on your sheet.

However, to get you started, I have attached a demo that I think does something very similar to what you are trying to achieve.

Take a look and maybe you can adapt the code to suit your own worksheet.

Re: Using checkboxes to add/remove values to a table

Thanks Tim.

I have treated the "Table" as if they were 4 separate columns with a variable number of rows of data in each column. For the purposes of the demo, the table is on sheet 1 in columns A to D. Row 1 of the table contains "Class 1" to "Class 4" as column headings.

You can use a named range but there are some complications. Before explaining that, I'll just step through the code in a bit of detail so you can see how this works.

The FetchMatching routine does the work of looking through the table to determine if the text you entered is found in one or more of the columns. It uses the "generic" FindInRange routine to do the actual searching. The FindInRange routine needs (obviously enough) a range to work with. The range in my original demo is determined by working out the last empty row in the column and using row number 2 down to the last non-empty cell as a range to search (row 1 is assumed to be a header). You can fairly easily use a named range here.

The AddorDeleteData routine does the work of adding or deleting the cells that match the test string you typed in on the userform. Right now, it works in a relatively straightforward fashion, finding the last row as explained above. If you want this to use a named range you need to be aware that the overall length of the named range (ie, the number of rows) may grow or shrink when you add or delete a cell in one of the columns. So the named range needs to be adjusted accordingly, this is where it gets a little complicated.

That's a whole lot of explaining but won't get you much further if you are not familiar with VBA. So I have attached another demo using a named range called TABLE. For the purposes of the demo, I have moved the data away from the top left corner of the first sheet so you can see that the code will work no matter where the table is (ie, the code works purely on the named range now).

Again, not knowing exactly what you are trying to achieve, my code makes many assumptions. Specifically, it still assumes that there are 4 separate columns with a variable number of rows of data but the all the data is bound by the named range eg, in my case the named range is B3:E10 because column C has the most rows including data in row 10 (the other columns don't).

Of course, the other thing to remember is that any data outside of the named range (even if it's in the one of the columns) will be ignored as the code only looks at the named range.

Re: Using checkboxes to add/remove values to a table

OK Tim, an updated version of the demo is attached.

This one shifts the cells up rather than deleting the cell as you requested. This will prevent the data below the table from being "pulled up". However, remember that the way this code is written at the moment, the table can grow (ie. the rows can increase) if you keep adding text values to the columns. This has the potential to overwrite data below the table.

Using a multi-select listbox is an alternative to checkboxes for what I think you are trying to achieve but that is something you will need to try out as I don't know your experience level with listboxes.

The main thing to pay attention to in the userform in my demo is the use of the EnableEvents variable as this determines what happens in the onchange event for the checkboxes. For example, the btnGo_Click routine updates the values of the checkboxes so EnableEvents is set to false during this process. If there was no EnableEvents variable then the checkbox onchange event would happen as the value of the checkboxes are changed in the btnGo_Click routine - this means data would be added or deleted when in fact you just want to update the value of the checkboxes.

Everything is else was intentionally written generically enough so that it could be ported to any project.

I am currently using Excel 2003 at home and have no immediate access to Excel 2007 or 2010 so unfortunately I am unable to open your sample spreadsheet (it gives me conversion errors when I use the Office 2007 compatibility pack).

Re: Using checkboxes to add/remove values to a table

I'm sorry Tim, without a better understanding of what you are trying to do, it is a bit difficult to respond to all your questions.

To answer your last question first:

Quote

Also - if I want to add a new table to the same userform (if the first one was say 'class' the second would be 'block') would I need to basically copy everything in Main and all the cbWind code a second time? Then tie them all (the same way I did with the Class) to the new table?

The short answer is "No".

The module called Main contains generic code that can be used by multiple userforms with some small tweaks. In my last example, the range name "TABLE" was essentially hard coded in the Main module. With some small change, the named range can be passed in to the FetchMatching and AddorDeleteData routines. Then each userform that needs to use these routines can pass the appropriate named range in and the named range/s can just be stored as constant/s in the userform.

A revised version of my demo code with these changes is attached. With this change, you only need to have one copy of the code in module Main (this is the reason for separating this code into a module in the first place). You will see that the named range is now a parameter to the routines in the module Main and the named range "TABLE" is stored as a constant in the userform.

Then the code that is in the userform in my demo needs to be copied to each userform that needs this functionality.

I don't fully understand your other question:

Quote

Quick question though - I have a delete button - which basically erases the tbName from the Name list - what code do I need to add to that button to make sure that it erases the name in the Class table under the classes that to which its attached?

I am not sure what you mean when you say "it erases the name in the class table...". I think you mean that pressing Delete will remove all the instances of that name in each of the columns within the named range in question.

If so, you need to make sure EnableEvents is true and then set the value of each of the checkboxes to False. As you set each one to False, each of the corresponding checkbox onchange events will be called and will actually perform the deletion of the text in the table.

A simple way to remember how EnableEvents needs to be set is that if you want to set the value of the checkboxes after you search for matching text, EnableEvents needs to be False. If you want to add or delete data when you select/unselect a checkbox, EnableEvents needs to be True.

I hope that answers your questions, I am starting another job in the next couple of days so I won't have as much time to look at this Forum for a while.

Re: Using checkboxes to add/remove values to a table

No problems Tim, glad to help...and China's not that far from Australia!

Try your hand with a multiselect listbox at some stage instead of multiple checkboxes, the coding will be simpler as you won't have multiple events to contend with (so no more onchange event for each checkbox, just one change event for the listbox). But you'll need to familiarise yourself with how they work.

So as a final bonus, I have attached a version of the demo that uses a multiselect listbox instead of checkboxes so you can see how this might work. Only the code in the userform has changed.