Multiple Selections in Multiple Dependent Drop Downs

[jade1779]

Please help!!! I apologize, I don't really understand VBA even after going through some online video courses so I haven't been able to write the code to do what I want. I want to be able to select multiple options in a dependent drop down. I want this available in every cell of 3 different columns (Columns K, M and O). I want each option selected to remain in the same cell but on a different line. So for example, Cell J2 says Body (what area of the body). If for example, I select Neck, then in cell K2 I have a drop down with several options but I may want to select SCM and scalenes and erectors. I will attach my workbook (it has no data in it yet) and on one sheet I will show what I want the end result to look like as well as highlight the relevant columns where the data will appear on my Month tab. I have all the named ranges in my first sheet, labeled Lists, where I have also created a Listbox for each named range because I'm not sure how I need this set up in order to write the code correctly. The second sheet, labeled July, shows the sheet where I want all the data to appear. The End Result tab shows how I want the multiple selection to behave once the code is written.

[jade1779]

I forgot to say the dependent drop downs already work, I just can't figure out how to make it so I can do multiple selections and each of those multiple selections will appear on a new line in the same cell.

[torachan]

Nothing like jumping in the deep end when you are learning to swim,
There are no on-sheet formula - everything is in the UserForm code - I have only coded the basics - only the 'ADD' button works at present.
Until you have looked through the basic code I will leave all the error checking, date formatting etc., until later.

[jade1779]

This works perfectly Torachan. As far as date output it should be MM/DD/YY. All of the other outputs are formatted fine. The only problem I have is I have zero familiarity with UserForm so while I can look at the code, I don't understand everything that's going on. Like I can't even figure out where you tell the code to put the data once it's been input in the UserForm. And I am actually going to need this to work on several identical sheets because I'll have to have one sheet for each month of the year. I thought I would just be able to copy the code from one sheet to the next and edit but I don't know how that works with the UserForm. Also, rather than update could it be a next button where after you hit add it would clear the form for the next entry? Or is that what the clear button is supposed to do? Also, just so you know the month tabs won't be strictly for those months because for example our July goes from 26 June to 24 July and those dates are not the same month to month.

[torachan]

You are falling into the trap of wanting to see everything on individual sheets, this is the one thing that users unfamiliar with a database approach fall into.
The sheet is the storage container not the visual interface - it is all about efficient data storage - a sheet is capable of storing over one million rows of data.
Using a UserForm interface you use ONE sheet and extract your reports in whatever format needed (i.e. between dates or with persons names etc.,
Just think of the ease, no copying code, no coping formula everything self-maintains.
If you decide to use a UserForm approach I will annotate the code then it is almost like reading a book

[jade1779]

Ok that makes sense. Would I be able to do pivot tables this way? I've only just recently learned how to do them by selecting all the data to use. If there's a way to input the data in order to pull all the info from a range of dates then I would love to use this workbook that way. Thanks very much! Sorry I'm such a newbie.

[torachan]

Put a sample of relevant data into the DATA table using the form, then use it for a pivot table - the table is no different to a normal table - does it work ?????
The main criteria is that you do not extend the DATA table manually below the last row of data - this table expands dynamically when the data is transferred via the userform.

[jade1779]

I'm sure that works fine, although I haven't tested it. My concern is just being able to select all data in a specific date range since there will be multiple users inputting data, the dates won't necessarily be in date order and would need for example all the data from 06/25/21 to 07/24/21 for monthly pivot tables for reports. Unless maybe this could be solved by breaking the month day and year into different boxes in the form?

[torachan]

Use the power of Excel - simply extract between two dates - no need to break the dates down.
I have attached a speed checker that I used to demonstrate the creation of import files into accounts and invoice files a few years back.
It may need the date in UK format, however put a number in box N4 (say 100,000) press 'Fill Array', when array has filled press 'Start Sort'
Now look at sheets 'invoice' & 'sage' you can see the filtered reports - far better to 'tailor' your reports than messing about with 'pivots'

[jade1779]

I'm sorry I don't understand what you mean about extract between dates. We use the pivot tables to make it easier to create the graphs for the reports. I followed your directions and understood how the sort function works, I just don't understand how that would translate in order to get the specific data needed for the graphs. Examples would be making graphs of what percentage of visits came from which squadron, how many of each squadron were pilots and how many WSOs, etc. for the month that falls between specific dates. I hope this helps explain what I'm trying to do.

[torachan]

fill the sheet with typical data and a mock up of what report you want - pivots are OK but need to be refreshed every time data is added - whereas custom reports are tailored to the data available without need to maintain.

[jade1779]

Ok. I think I can figure out how to do it. Refreshing pivots isn't a problem as we just use the data for the month and then start over with the data for the next month. We don't ever change anything on the old stuff. We use the excel stuff to be able to make the charts and graphs for the reports we write in Word. Then we just combine the data every 3 months for the quarterly reports.
So since I should be able to do the pivot tables this way I think I'd like to use the UserForm.
By the way, thanks for all your help. And I really am sorry I'm such a newbie at this.

[jade1779]

Would it be too much to ask for you to fill in the rest of the userform code so I can see how you did it? Thanks

[torachan]

Hi.
To complete the form/code I need to know which column is the primary key, i.e. what will you search on to locate an entry for updating/altering/deleting.
My instinct would be pilot/wso, as there are likely to be more than one entry for this 'person' there would need to be a secondary 'key' to isolate the exact record, would this be squadron ? these two keys would then become columns 1 & 2 of the table - when you have confirmed this I will complete the form and annotate the code so that you can modify in the future if you wish.

[jade1779]

The columns you're looking for would be date and UIC. Thanks

[torachan]

Give the attached a trial - unique ID should auto increment for each addition - first press 'CLEAR' then enter data and then press 'ADD'
To update or delete select from the unique ID dropdown to see data.
I will annotate the code when any alterations are complete.

[jade1779]

Thanks torachan! sorry it took me so long to get back to you. This works fine. I'm not sure how to notate thread as solved but thanks again