populate textbox from a listbox when data contains either decimals or cel properties

[Djeetn069]

2 problems i want to get out of the way...

My first problem is on how to nicely align the data in a listbox. My code appears to be wrong even when i can't see any mistake what so ever???
Maybe i am overlooking something, which i think will probably the case... You'll find my example attached.
(To use it correctly you'll need to make a folder called "test" on you local C: drive an place some random *.txt files with different name lengths in it.)

2nd problem: I am looking for a way on how to populate a textbox with data from a listbox that has been populated by a dynamic range.
Actually i got this to work, except when the value in my lisbox contains either decimals 0,00 (comma) or if the data contains special cel properties,
like 0 "pc" or 0 "mm" at the end of the value.

I made a small test file on where you can find both problems shown. (see attachment) test.xlsm

If anyone could help me out, i would be very grateful!
Thanks in advance!!!

[rylo]

Hi

Couple of thoughts for 2nd problem.

1) Add an en extra column that can be hidden, that has the row of the source. That way you will know where it comes from, so don't have to do the find.

2) As don't seem to have any formatting on the first item (aluminium sheet) it seems to do the find successfully. From that find, you can get the row of the data (rng.row). So you don't really need to do any more finds, and you could use that will the cell(row,column) approach to knowing where the data is and can action accordingly.

HTH

rylo

[Djeetn069]

Hey Rylo,

I like your way of thinking! I could indeed force the outcome in the way you propose, however i am wondering if this is really the only solution possible?
I'll keep it in mind but would love to see on how it can be done without having to "cheat"! And what about a value with decimals, it will look a little silly if i provide another column for decimals only...
As i said, this is a great backup plan though!

[rylo]

Hi

I'm not saying that these are the only ways to do it - just ways I can think of to do it. You don't need to have another column for the decimals, as once you have the row number (regardless of which of the above methods you use to get it), then you know where all the items are in the source sheet.

rylo

[jaslake]

Hi djeetn069
I haven't tested the code thoroughly but it has been tested.
Please note that I've changed ListBox to three columns to get the alignment. I've also set Rng3, Rng4 and Rng5 to look at

Please Login or Register  to view this content.

in the Subtract/Add Button Codes. Let me know of issues.

[Djeetn069]

Hi John,

Concerning the problem I had with the alignment of the populated listbox, this works exactly as I want now, so thanks a lot!
The second problem hasn't been solved with the proposed solution though. The problem remains if a part of the value from the first "Lookat:= XlPart" is defined elsewhere, for instance: select the last row in "test v2.xlsm" and try to add 1pc. You'll notice that it will change the dimensions value of the row above.
I've also noticed that the problem also remains when i try to add a pc to the 1,5mm thick sheet. My best guess, because of the decimal number... you'll notice a msgbox "material not found"...Any more ideas?

Your help is much appreciated!
Sincerely,

[jaslake]

Hi djeetn069
Does your actual worksheet have the same structure (number of columns, etc) and layout as your attached sample (column headers, etc)?

[Djeetn069]

In fact it does. First line = header, the rest is practically the same except that the value can variate. For instance, the First row can also contain Ø 4 instead of 1000x2000. Everything else remains...

[jaslake]

Hi djeetn069

Is the ONLY thing we EVER want to change is "in stock" quantity in Column F?

[Djeetn069]

Good question John! Because for now i'm stuck on that issue indeed. But to see the complete picture, later on i want to add following option: when you manually write something in the textboxes, it will also appear as a new line in the listbox. (let us say, as beeing a new type of sheet). But you can put it that way that FOR NOW, i only want to change the stock quantity in column F.

[jaslake]

All right...let me play with this for a bit...I'll get back to you.

FOR NOW, i only want to change the stock quantity in column F.

[jaslake]

Hi djeetn069


It's going to take me a bit to sort this out...perhaps a different approach...get back to you.

[jaslake]

Hi djeetn069

Try the code in the attached. Let me know of issues.

[Djeetn069]

Hi John!

The code you've provided does the job very well! However i wished i didn't mention in my previous reply

"For now"...

Because as i've told you before, if i manually type the materialinfo in the textboxes -because let's say i want to add another type of aluminium sheet to the database- I can't use the code u provided because it will look at the selected item in the listbox while in fact it should first look if the item isn't already in the database... Therefore i've used the findstrings so the program could search the database for the material from the textboxes and if it finds the material (like aluminium) then it should look if the sheetsize of the aluminium already exists, if it does then it should look for the finishing and if that does then it should add or subtract the value. If any of the here before mentioned doesn't apply, it should add the sheettype and finishing into a new line in the database. This is in fact exactly what i need, so i now know that it's wrong to only ask for the solution on which i'am stuck because as an outsider you've got to understand the compleet picture... Is there a work around with the code you've provided because it does the trick correctly?

I think it would be best if I attach the original file aswell so here you go... to be saved at "C:\test\" Stock.xlsxBeheer.xlsm

[jaslake]

Hi djeetn069

Is there a work around with the code you've provided because it does the trick correctly?

There's always a work around...just have to find it. Let me look for a bit.

[jaslake]

Hi djeetn069
Before I ponder this issue...a question has been nagging me and I'd like to know. Will you have ONLY Aluminum sheet as a Material in Column A of Stockdata? I suspect not but then

as an outsider you've got to understand the compleet picture

If your answer is no then we need to solve this issue as it's overriding to other issues. If yes...then we move forward.

[Djeetn069]

Thanks John,

I've just uploaded the original files aswell. If you take a look at it, maybe it will be a little clearer to understand what i try to achieve...
As you will see, there will be more materials...


Thanks in advance!!!

[jaslake]

Hi Djeetn069

I've just uploaded the original files as well

Where? Did you attach it to a different post?

Never mind...I found them...I'm terribly color blind...missed them.

[jaslake]

Hi Djeetn069
Well now...this is a bit different than what we've been dealing with. I'm back to square one...it'll be a long while, if at all.

[Djeetn069]

Hi John,

No problem... I'll try myself once more, but it isn't easy if you're not that familiar with VB as i am. Anyhow thanks a lot for looking into this matter and if you
should find something or think of a better way to achieve my goal, please keep me informed!
Your help is much appreciated! If you give up, well, let me know as well .

Cheers m8...

[jaslake]

Hi Djeetn069
No, have not given up on the issue...have some ideas...no clue if they'll work. I'll get back to you.

[jaslake]

Hi djeetn069
If the item isn't found should the User get a message asking if they wish to add a new item or should the new item simply be added?

[Djeetn069]

Hey John,

I did provide a msgbox which asks the user if he would like to add the material to the database. I thought it would be best to inform the user of the action he's performing.

[jaslake]

Hi djeetn069
I'm sure the message box was/is there somewhere...since I don't speak or read the language, I inserted my own. Try the code in the attached...it's been substantially re-written. I've beat it up pretty good but only you know what it is you wish to do.
Please note, if a Product ListBox is empty, it's hidden in the Initialize Event. This may or may not be an issue for you. Play with it...let me know of issues.

[Djeetn069]

Hi John

Let me run some tests, I'll get back to you as soon as i'm done but it will be tomorrow the soonest... fingers crossed.

Best Regards,

[Djeetn069]

Hi John,

You've nailed it!
There's no way i can put into words how thankful i am, really.
I could never ever have found or put together a code like that...never...
So again, many thanks for all the work and effort you've put in!
To make the listboxes that aren't populated invisible is a good idea indeed.
With a little tweak here and there it will do exactly what i was looking for. So Superb...

Well, I'll finish the last minor issues, and hope that i don't have to bother you again

Most sincerely,
G.

[jaslake]

Hi G
You're welcome...glad I could be of help. A bit of advice, if I may. When you post a workbook please make certain it's of the same structure and data types of your "real" file. This prevents false starts.
Having said that, this has been an interesting exercise...hope it works for you.

[Djeetn069]

Sure thing... I thought, lets not make it look more chaotic than it already is, so i made a small example of what i wanted instead.
Afterwards i found out it wasn't the smartest thing to do indeed. But hey, you got it and how... Thanks again!