Move listbox contents to row, horizontally, starting at chosen cell.

[IMM Tech]

So this is my first jaunt into listboxes.


The only thing I have left to do is to figure out how to paste the full contents of ListBox1 (on a userform) into the row designated "tRow", starting at column C and extending horizontally to the right.
All of this data is going into "Table1" and I have existing code to add a new table row, then return the number of that new row to "tRow".

The target worksheet is named "Mold List DB".

Ultimately it should look like this (I apologize for the wide image):
Capture3473.JPG

The descending quantity of items in each row shown in the image is irrelevant, eg; row 1 may have 1 item while row 3 may have 100 items and row 5 again only 1.
The "selected" cell in column D is also irrelevant.


I am already utilizing the new table row and "tRow" variable to successfully add values to columns A and B in that row, and just need to fill C and beyond with the ListBox contents.

[torachan]

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.

[IMM Tech]

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.

I would but my company has some extremely arbitrary and vague rules about uploading literally anything anywhere, regardless of content. Sorry.

If I have time later I may be able to make something from home they wouldnt have a case for but today is not that day.

[IMM Tech]

After several hours of searching google with every combination of search terms I could think of I still have no solution here.

[torachan]

A file is worth a thousand pictures - probably something simple - why waste time.
torachan.

[IMM Tech]

A file is worth a thousand pictures - probably something simple - why waste time.
torachan.

Because, as I have explained, I literally cannot do this.
My job is worth more than this project is.

We all understand how easy it is to have something which is totally free of sensitive information but management's idea of avoiding the situation is to simply disallow the whole concept. As I said, if I get time (on my own time) to create an example file I will. That however I am not being paid for and thus has a much lower priority

[torachan]

You can waste your time - I am not prepared to waste my time speculating and crystal ball gazing.
Your employer must have money to burn !
torachan.

[IMM Tech]

You can waste your time - I am not prepared to waste my time speculating and crystal ball gazing.
Your employer must have money to burn !
torachan.

I have said that a few times. Burningmoney.MP4

At any rate, here you go.



What I am looking for needs to go in commandbutton5_click.

I have denoted the two locations where it must go with

Please Login or Register  to view this content.

It should just be the same code in both locations.

The function of button 5 checks the value in combobox2 against the first column in the table and hits it with an if/then.

If it finds the value in the table:

• Write row number to "MNRow"
• Deletes the row at "MNRow"
• Adds a row to the end of the table, and writes that row number to "tRow".
• Writes contents of combobox2 and textbox1 to columns A and B in "tRow".
• Puts the entire contents of listbox1 into "tRow" starting at column c and extending as far to the right as necessary, with one item per cell.

That last item is what I can't figure out.


If it doesn't find the value in the table, it doesn't delete anything and just adds the new row and contents of the userform.

[torachan]

You need to re-visit your app and decide sheet structure.
At present you have a table, but some of your search/delete procedures are applicable to working with ranges.
The fact you will be storing varying numbers of columns per row virtually excludes the practical use of tables (they are really intended for regimented/structured use).
Therefore data handling should be exclusively by range referencing, to use tables would become very complex as you would have to query size for every manipulation.
With a range NOT a table try the code below to add the data from your form textboxes/comboboxes & listbox.

Please Login or Register  to view this content.

[IMM Tech]

I understand what you are talking about, and even grumbled about it while I was doing it. I was using this explicitly as an excuse to start messing with tables since I hadn't previously ever done so. As a result, most everything else is referenced to the table. The stuff presently using ranges is basically what I wasn't able to figure out how to do through the table itself.

As for future use, the sheet as you have it in front of you is entirely "finished" and functional other than this listbox issue and a few minor userform behaviors. 99% of the roughly 200 tools we have run less than a dozen parts, and there's only 1-2 which need any significant number of columns so I don't need anything additional added which would increase complexity, as you stated.

I attempted to insert that and stop using the table but after the 5th error that required switching to ranges I tried just using it on its own with the table and have a type mismatch here. Yes I understand you requested to not use said table and that might be related.

Please Login or Register  to view this content.

I went back and renamed my previous uses of x to xV but that solved nothing.

Is there any way I can utilize that without needing to rework every other reference to the table in the project?

I am objectively terrible with VBA but sadly its the only "approved" option I have for the time being.
Basically, I create something like this and pester everyone to use it a few times in read only mode and eventually I manage to make the case to pay to get it built into our existing ERP database.

For now, my work day is finished, but I appreciate the response.

[IMM Tech]

Got it to work as required with this modification. I realized that first line I had the type mismatch on was just spitting out the first empty row which is a variable I was already getting with "tRow".
Simply inserted my already known row number variable in place of that. No need to modify anything else.

Please Login or Register  to view this content.