This might be a bit tricky...
In a UserForm I have 2 multi-select ListBoxes. And a Go button.
Listbox.A is populated with a list of names from the active worksheet.
Listbox.B with 'list.B' (not important where from).
The goal is to return selected values from Listbox.B, to empty cells to the right of each name selected from Listbox.A
I would show you what code I have so far but I'm afraid you might laugh at me :/
ListBoxes.jpg
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
To get you started, this way you put the selected values of Listbox B in an array for later use.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Well, why not let us laugh? Don't bring yourself down, everyone started with 'laughing code' If I look at my code written 15 years ago I laugh my head off.
Your question: this means that you want to add all the items selected in BOX B to an empty cell in the row(s) of each selected item in Box A so there will be duplicated names from Box B in every selected item from BOX A but you do NOT want to have duplicates in the same row ? IS this about it?
I suggest you attach your file (dummy data) with the macro you have (so we can laugh) makes it easier to start with
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
bakerman2 Thank you for the advice.
Keebellah, This is exactly correct.
I have created dummy files as suggested. My attempt at the code with explanation is inside the UserForm. Laugh away!Your question: this means that you want to add all the items selected in BOX B to an empty cell in the row(s) of each selected item in Box A so there will be duplicated names from Box B in every selected item from BOX A but you do NOT want to have duplicates in the same row ? IS this about it?
Okay, got the file. Will get back to you
See if this gets you started.
Please Login or Register to view this content.
Looks great bakerman2
bakerman2, that's working pretty well!!
There are a couple of things like the 'ClearContents' which I removed. And I'm having a go at incorporating a line where as well as keeping the old entries, it offsets the new entries alongside the old ones.
I'll let you know how I go.
Thank you!
Ok I tried and I just can't seem to incorporate it successfully. I feel like it should be quite simple!
This is my tried and true line for offsetting values to next blank cells to the right:
Please Login or Register to view this content.
In my head, the obvious solution should be to replace A with the segment from your code (.List(i) + 2, j + 2)
I've fiddled with it here and there but still no luckPlease Login or Register to view this content.
Yes, but you also want to avoid duplicates?
And another question, limit the max to 6 columns?
Maybe there's a simpler way but I used bakerman2's code and added my part to it.
Added a MSgbox
There should also be a check if anything has been selected but that's for you
Keebellah, I hadn't thought about avoiding duplicates but that is a wise addition.
And I'll probably have the max limit higher than 6 but I can adjust that easily enough myself.
The code is now working perfectly as far as I can see. Thank you (bakerman2 included) so much for your help and patience!
I have one last question though. How come you decided to use code to Initialize the Listboxes as oppose to creating a RowSource in the Properties?
Damn it. I spoke too soon. I've applied everything to my project and have realised the issue lies in this line:
It worked for the dummy because .List(i) represented a numerical value. But in my project the value has letters in it...Please Login or Register to view this content.
So i'm getting a type mismatch error
I assume I would have to declare ".List(i)" as a string some how?
That shouldn't matter, what's the exact error message?
The problem lies in the i and x, approach must be modified
The problem is the that the value of i cannot be used as row number, need to use a long for this
Okay, try it now, should work
Keebellah, ok i see what you have done now. And yes it is working with my project now also!
Thank you.
Fingers crossed I didn't speak too soon again :/
One thing to know is that if you do not select anything you can press ok and confirm you want to clear all previous entries to clear the table
ok great, gotcha.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks