MultiSelect Listbox in Userform to copy data to worksheet

**aarodn** · 05-30-2013, 07:52 PM

Hi all,

I have been gettin down and dirty with some VBA lately, and I have hit a roadblock. I cannot figure out how to modify a userform (multiselect) listbox to copy data onto a worksheet.

The userform will be a way for the user to select the time period for which several graphs will be created. Right now I have the code set up so that a pivot table is created from a master log of data, and multiple data sets are pulled from this pivot table to prepare for graphs. one of these datasets captures the time period, so I have a "Period" sheet that gets created with this time period data pasted into column A and named "HowLong".

The listbox RowSource pulls from this named range so that the user can select what time period they want graphed. In order for my code to correctly graph the desired period, I need to have the checked checkboxes copied to the "Period" worksheet. Or maybe (probably) there is a better way to do this?

Bottom line is:::I need my code to recognize the user selection and "select data" for the graphs accordingly.

Any help or direction is greatly appreciated.

**Tinbendr** · 05-31-2013, 10:43 AM

Originally Posted by aarodn

... I need to have the checked checkboxes copied to the "Period" worksheet. Or maybe (probably) there is a better way to do this?

You'll need to supply a sample sheet so we can see what you're talking about.

**aarodn** · 06-03-2013, 05:32 PM

I have an attached sample workbook that should give you an idea of what i want to do.

i would like the user selection from UserForm1 to be copied onto a new sheet so that i can graph accordingly.

**Tinbendr** · 06-03-2013, 09:32 PM

Userform1 posts the select items to the period sheet.

**aarodn** · 06-04-2013, 01:23 PM

thank you Tinbendr!

after looking at the code, i have a few questions:

1)you removed the need for the use of the named range "PeriodList" by using the "Unique" collection, right?

2)i think i understand that in the code for "Post" button, 'A' is a variable that is used to loop through the items on the listbox. Is 'I' used to increment the row that the selection(s) post to? it would be really helpful if you can help me better distinguish between the two.

**Tinbendr** · 06-04-2013, 02:13 PM

1. The reason for that is so as new quarters are added, the code will automatically add those to the listbox. This just eliminates human error in keeping the a separate list up to date. One note about the collection. You'll notice it's surrounded with the On error Statements. You can't add a duplicate item to a collection. We take advantage of this characteristic so that in the end we have a unique list. The catch is that we have to skip the error. That is what the On Error Resume Next line does. Then after we pass the Unique.Add line, we turn the error coding back on to see other errors that might occur.

2. You are correct on both points! In a listbox, the only way to find out which ones are selected is to loop through all of them and test the Selected property. 'I' is used as a separate counter to correctly position the data on the next line. You couldn't use A since if it's not selected, you would end up with blanks in the sheet.

**aarodn** · 06-04-2013, 05:06 PM

Thanks for clearing that up!

Say i want to get input from the user so that they can provide the total number of creditcard events for each quarter. they would enter this data in the userform and it would be posted to a sheet so that calculations could be performed using these numbers. i am trying to accomplish this with an input box and the same idea of for-next loop...but each input box would have a label with the name of the Quarter. i posted code below of what i have so far...when i run the macro i can see one input box but when i go into VBE i cannot tell if it added the right number of input boxes.

Please Login or Register  to view this content.

**aarodn** · 06-04-2013, 05:47 PM

so i found this link and i think it is addressing what i am trying to do. using the code as a template..i modified it into the code below.
i get the following error message:

"Run-time error '91': Object variable or With block variable not set">>>>when i select 'debug' it takes me to the code for the button where i have show.userform

Please Login or Register  to view this content.

any suggestions?

**Tinbendr** · 06-04-2013, 07:10 PM

OK try this one. See if the new userofrm is heading in the right direction.

**aarodn** · 06-06-2013, 03:52 PM

Sorry... I think I may have done a poor job explaining my thinking!

When I say I want the user to enter the total number of creditcard events, I mean for the whole quarter. (say 100 in q1-2012, 90 in q2-2012, etc.) This is necessary because the data that will be in the workbook will only be a portion of the total data of company creditcard expenses (just the 'unapproved' transactions). so by getting this outside info we can tell % total expenses that were approved vs unapproved, and other big picture analysis.

our first userform allowed us to provide the user with a dynamic range of Quarters to select from...this second userform needs to ask "how many total events?" for each Quarter they selected. so it needs to have the ability to be dynamic in size. Larger (with more controls) if they select every possible Quarter versus only a few or just one.

Sorry to drown you in details but I hope I am making more sense! Really appreciate the help!