Move data between listboxes on Userform and update tables on a worksheet

[colin7]

Hi,

I have 5 tables all named table1 table2 etc. They are all on the same worksheet and in the same columns placed beneath one another.

I have created a userform which has 5 Listboxes all named listbox1 listbox2 etc. Each listbox is populated by the tables eg listbox1 is populated by table1.

My goal is to select multiple items (rows) in a listbox eg listbox1, click a button captioned "Move to", a new userform will pop up allowing me to select another listbox eg listbox3, it then moves the selected items to listbox 3 and updates the tables on the worksheet showing the same changes.

I hope this is clear enough.

Thanks for any help in advance

[Pepe Le Mokko]

Welcome to the forum

Please 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.

[colin7]

OK, I have uploaded a file if you could look at for me please.

Thank you

[colin7]

could someone help me with this please. I appreciate any help. thanks

[colin7]

anyone help me with this please? Does anyone know if what I am asking is even possible? Thanks

[bakerman2]

Let's try this for starters.

[colin7]

@bakerman2

It is all working perfectly , you are a genius, thank you.

I have a few minor changes if possible.

- For the "Delete" and "Move To" buttons could I select multiple tenders (rows) and move or delete them all at the same time.
- When I am moving a tender to the "Submitted Tenders" table the last column will be "value" in place of "Action Points". Could a userform pop up asking for the value which I can then type in and press enter if I am moving tenders into the Submitted Tenders table?
- I don't know if this is possible but is there a way you can select a tender and drag on drop them into order via the listbox on the userform?

I can't thank you enough for your help, its has really helped me a lot

Thanks

[Kenneth Hobson]

I think all of that is doable in VBA except the drag and drop. If you wanted drag and drop, I would show the user two views with each table at the top. With Userform1 shown as vbModeless, they could then do what they needed directly. A userform might let them pick which two tables to show side-by-side.

Changing the last columns inserted values is a bit more work.

In the future, I would recommend naming your controls to be less generic. e.g. I am not sure which Table, Table4 and Table5 are in relation to your Userform3's combobox list. Table5 comes before Table4 in the worksheet. Of course one advantage to suffix named controls with numbers is that it makes iteration easy. When naming Tables, use an underscore for space characters.

bakerman2 did a great job for you. I too just worked out a single listrow Move method. I will post a multiselect move method if no one beats me to it.

[colin7]

Kenneth, thanks for your reply! I will take your comments on board for the future as I am only learning VBA. For the drag and drop, the date of a tender will change constantly so I will then need to move them in order of earliest date first. So if I edit a tender and change its date then it will need to fit back in order of earliest date descending in that column. I am not sure of the best way to go about doing this. Would a code be possible to put dates in order after they are edited?

bakerman2 did do a great job and I'm delighted with how he has got it working.

Thank you!

[Kenneth Hobson]

This is based on the post #6 file. It allows multiple selection moves or deletes.

I will look at adding the feature to change moved row's last column value for the one table. Adding the sort feature in the moves/deletes routines should be easy enough.

[colin7]

Perfect Kenneth.

Could I have a msgbox asking if I want to delete tenders before deleting and move before moving.

Is it simple enough to change it so I can select multiple without holding ctrl key?

When I click the Move Tender button and select the drop down list can the table which I am moving not be included in the list so I can not select to move it into the same table?

Again thanks for your help with this, much appreciated!!!

[Kenneth Hobson]

Could I have a msgbox asking if I want to delete tenders before deleting and move before moving.

Yes

Is it simple enough to change it so I can select multiple without holding ctrl key?

Yes. The one that I used is a Windows standard feature. You can use Shift+LM click method to select big chunks of continuous items. Use Ctrl+LM click to select/deselect single items of course.

When I click the Move Tender button and select the drop down list can the table which I am moving not be included in the list so I can not select to move it into the same table?

Yes, I was going to do that but did not know if you might want all tables offered as table out.

Let's get these other features added first...

[colin7]

Yes that's great Kenneth thanks.

So will the fmMultiSelectMulti not work?

[colin7]

Sorry I am being fussy but I just want to have this set up as simple as possible!

[Kenneth Hobson]

Yes, that property works fine for single off/on selections. I used the other method for extended selections but you can change it to suit.

I did not test this much. I added an informative MsgBox() feature for Moves/Deletes. I also added a sort routine and used in the Moves/Deletes routines. You can see how I used it and add it to the Edit routine if needed. It may be a testing thing but I noticed some numbers vs. text dates in the sort column 1.

For the rows moved to Table4 to modify the last column's value feature, did you want an option to change it for all moved or for each one separate? If the latter, that could get tedious.

Meanwhile, I will look into the feature to not list the move from folder in the move to list.

[colin7]

That works perfectly Kenneth. I am really happy with that now!!

Will the code be the same to do the sorting of dates on the edit routine?

My idea was when moving to the submitted table that another userform pops up with textboxes populated with just the project names of the tenders being moved and textboxes to enter the project value beside them. Each value will be different. The last column in the submitted table is the project values where as the last column on all the other tables is Action Points.

Thanks Kenneth!

[colin7]

Also I forgot to mention, column 1 is always dates but sometimes I would have a time after the date beside some projects for example 03/03/19 12pm....Will your code still work to sort the dates ignoring the 12pm?

[Kenneth Hobson]

Be sure to test this.

I removed the from Table list in move to table userform3.combobox list. I set the 5 userform1 listboxes multiselect preference. I did a few other minor things as well.

I will have to think on the change last column values for move to that one table. We may be able to use the Edit fields with some thought. e.g. Put the moved last column values to that one table as say "EDIT". An autofilter method could then be used in some way making use of the Edit controls or some other means.

True dates and times are just numbers. Times are just fractions of a day. e.g.

Please Login or Register  to view this content.

One has to be careful when putting values from a control into a cell. Most control values are simply text strings. Dates are numbers but cells can be formatted to show them as if they were a string. So, a userform's Textbox value is just a string. If done properly, Excel can sometimes guess that a string value is really a numeric value. CDate(controlStringValueHere) can work sometimes. So, if you mix string dates values with the true numeric values, a sort will never work out right in common practice. It is shame that when Excel upgraded, we lost a good and stable VBA ActiveX date control.

[colin7]

Hi Kenneth,

When running the Move To routine I get a runtime error 438 Object doesn't support this property or method. It highlights .SortFields.Add2 Key:=Range(Tbl_ & "[Date Due]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

I usually type the date followed by a time (eg 6/9/19 - 12pm) or (6/12/19 - 5pm) but I could put in another column so the times are separate from the dates to help with sorting?

Thanks!

[colin7]

Kenneth/Bakerman2,

I really appreciate everything you have done to help me with this so far but could either of you help me get it finished by fixing a few wee things please.

- The dates in the first column of each listbox are in US format, can this be changed to UK format ie dd/mm/yy

- Can the last column in the submitted table be formatted as currency in the listbox?

- When running the Move To routine I get a runtime error 438 Object doesn't support this property or method. It highlights .SortFields.Add2 Key:=Range(Tbl_ & "[Date Due]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

- After I edit a tender the date does not go back in order as I think you were trying to explain..Is there a way around this to get it working?

- If a tender is moving to the submitted table can it prompt me to enter a value for the project before moving it?

Again any help is greatly appreciated and I would be willing to pay you a small fee for your time spent helping me with this

Thanks

[Kenneth Hobson]

I did not get the post #19 reply I guess or trashed it accidentally with my junk mail.

As I explained, dates and then add in times, are a pain when users run wild. I would suggest using CDATE() when they leave that textbox control and attempt to fix it first. Obviously, one would do it in the parts where it gets written back as well. You can avoid the bulk of the problems if you show the user the proper syntax to do it in a Label control. e.g. "d/m/yy - h am/pm" or "d/m/yy - h:mm am/pm". Of course CDate() uses the user's Windows Local Regional date/time settings.

Here is how to convert it if for the " - " problem. The string date+time would be the control name.

Please Login or Register  to view this content.

Once CDATE() is successful, you can put it back into the control using FORMAT(). FORMAT() returns a string. CDATE() will need to be used before writing back to cells too but if you handle it in the Userform, it should always be successful.

Currency format issue in the listbox can be handled but takes more effort. One method is to fill an array with values and then iterate the range and insert the cells property Text rather than the default, Value, back into the array. This is really an aesthetic thing though. So, leave it to the side until later. Don't loose the big picture.

The sort issue is likely related to your date+time issue. Excel can handle mixed strings and values sometimes in a sort and sometimes not. I suggest fixing the date+time thing first. Before that though, a manual cleanup should be done.

Your cells should have the same formats within the same column. I noticed that you mixed date formats within the same column. That could be confusing to some. e.g. 1/2/19 = 2/1/19 (m/d/yy = d/m/yy).

[colin7]

Kenneth, thanks for taking the time to reply to me. I understand the concept of all the things you have said but I am new to vba and only know the very basics. I think this would need someone that knows what they are doing to get all that working properly. Would you be able to get the codes working for me when you have some spare time? if not I will try to get someone else to have a look at it for me.

Again thanks for all your help with this, it's much appreciated!

[colin7]

Need help adapting this code