Forum Discussion
HELP WITH VBA OR FORMULA
msm66 First off, please don't create new duplicate threads.
As for VBA, of course VBA can do anything you can do in the sheet and more, but unless the VBA code is really small or trivial, the us volunteers here probably won't create it for you. But in VBA you can create custom forms for data entry (I believe a PC version it is still required to create or edit the form but that they will work on a Mac, but they don't work if using Excel online). You can also create active modules that will monitor sheet edits and make updates accordingly.
That said, I often try to achieve the functionality in the sheet to avoid problems with others being required to approve the macro functions before the sheet will work (and in some cases they can't).
My prior answer using drop down menus could be modified so each successive drop down list would exclude items already selected.
Another alternative is to add another column next to the list and just have the user type an "x" in that column instead of 'checkboxes' or maybe rank their favorite foods in that column (then you could even sort by that rank).
If you want checkboxes, they point to a cell so if you create that column next to the list and add checkboxes over each cell you can have each checkbox 'point' to that corresponding cell.
So if you take the suggestion of adding a column that they rank their favorites then you could use:
=IFERROR(INDEX(A:A,MATCH(ROW()-ROW($C$1),B:B,0)),"")assuming the food is in col A, the ranking is in col B, and this list is created in col C with C1 being a header line.
Various tweaks could be made to account for non-sequential and duplicate ranking and such if needed.
I am working on a project and it looks like you are an expert. Not sure how can be in touch. but let me know if you are available.
msm66 I am not an expert, when we talk about checkboxes. I've used them, but not too fond of them for various reasons, some of which may just be me not knowing/learning advanced use options for them. One of the main issues I have with them is the manual overhead to create and configure each of them. So I have attached a sample document that I think is what you are looking for and as you take it and expand it you can see/decide for yourself (unless someone else here might know and chime in with some tricks to make it easier).
In the attached I copied the lists you showed in your attached image (spelling errors corrected) into columns B, D, F and columns A, C, E are used to give spacing between the lists so the checkboxes can sit to left of each corresponding item. The checkboxes are then configured to control the corresponding cell it is sitting over top. The 3 "Selection" lists are then in columns H, J, and K also skipping a column between each. This space is nice in appearance but more importantly makes copying the formula easier. So try it out and see and if you like you can follow the following instructions to expand:
1. fill in the rest of you lists
2. "copy" or "add" additional checkboxes (tip: if you shift - right click multiple checkboxes you can then ctrl-drag them to copy many at a time)
3. configure each new checkbox to "control" the cell it is sitting on top of. Ctrl-Right click box and select "Format Control" then on the "Control" tab type the cell reference into the Cell Link field. (note you can click on the cell but I find that harder since the checkbox is blocking it and I'm faster with my keyboard)
4. The formulas under the "Selected" list are copied from row 2 to 6 (i.e. max of 5 items). If more are needed you can copy the formula down to additional rows. Bonus: I added conditional formatting to highlight a "Selected" column if it appears more items are are selected than can be shown in the "Selected" list. You can test this by selecting all 6 items in the first list.
Do you have any solution for multi checkbox list?
I Have over 50 checkboxes in one column and another over 50 checkboxes in the other column, and so on. In total, I have over 10 columns that each need over 50 checkboxes. Do I Have to link over 500 checkboxes one by one? or is there any way to make it faster?
I search and found some VBA codes but they mostly are for multi checkboxes in one column. like this one:
but it is not what I need. Any thoughts or tricks you know?
Sub LinkChecks()i = 2For Each cb In ActiveSheet.CheckBoxescb.LinkedCell = Cells(i, "B").Addressi = i + 1Next cbEnd Submsm66 Hi there again. I did say "One of the main issues I have with them is the manual overhead to create and configure each of them." I also tried to steer you to other options using drop down or simple "X" or numbers in adjacent cells. I'm not trying to be petty, just apologetic that I'm not much more help to your current situation. IF you created them in an orderly fashion you can create a macro similar to the one you found to assign the cells but if you are getting into writing a macro you can write one that monitors the page and on cell activation (i.e. click) toggle the background color and add/remove that value from the corresponding list. I don't think it would be too hard, but it is VBA code.
- Thanks for your response.
I did not quite catch when you mentioned it. When I get to the action I realized Holy moly. It takes me forever. the problem is I do know nothing about VBA.
thanks any way.
- I really appreciate taking your time and providing the best and most complete answer. This was exactly what I was looking for. I really like the date validation Idea you added to it.
Thanks alot