One listbox with variable content depending on data validated cells' selection

lcolgi: Sure it is easy :) Could you upload your file so that I can build on that?

Sid

Hey hi expert leader,

Here is my file. I cleaned the macro side as it was really messy. Hope you got what I wanted to do, it is sometimes hard to put into words.

Also I would like to add another challenge for you guys :) because after this, I need the selection of the user to appear in cell I10, and the other cells of line 10 to be populated with the corresponding line in the right table (I guess we can do that with offset). So for example if the user select in the listbox with data from line TL/N, the number 6, I would like the 6 to be in I10, 7 in I11, 8 in I12... and same on the other side, 5 in I9 and so on. The empty cells would be filled with ###.

In short, the whole process would be a method to center the table around the user choice (in column I), but the parameter used to center depend on a lot of other parameters.

Hope it is not too confusing,

Thanksssss.
MyFile.xlsm

Ok where do you want the listbox?

Sid

I need a listbox that popup from a button like a msgbox with two buttons "OK" and "Cancel". So I guess we should go for a userform to set up, however I have never done that and the related examples I used in the past are not as complicated as this one.

Thanks,
L

Update*

Is it challenging finally? Are you still thinking about it Sid?

lcolgi: Sorry to have left you in the middle of nowhere but the intention was not that.

Let tackle it now.

Sid

Since I am looking at this file after a long time, I have lost my chain of thought.

Depending upon the cells I4, I7 and I8 what criteria should I follow to display the values in the listbox?

Sid

hey,

Yeah, all this has changed a little bit by now... "expert exchange, an answer within an hour" aaah marketing. anyway, thanks for coming back, I am still interested by this piece of code, and I think I will be able to adapt it to my code if I have tips for doing the following:

- by clicking on a button a listbox appear, like in a userform with two buttons "choose" and "cancel" [i know]
- this listbox's content is variable on the selection in I4, I7 and I8.
      - if I4 is W then use the data from the upper table, if not lower one (on the right, in grey)
      - depending on I7 and I8, choose the row to take the data from: here is TL and NUMERIC so take it from row 2 of upper table (R3-AG3) and display all the options (which are not '###') in the listbox.
- the user then selects the one he wants/needs from this list and by clicking on "choose" this value should be returned in I10, with the corresponding values on the right and left of this one (same order, centered by the user).

Let me know if you need more precisions, thanks.

lcolgi: The basic code is ready. Let a small confusion. What is upper and lower table. Can you give me an example with cell addresses.

Sid

Upper is from R2 to R9 and all other columns, while R12 to R22 and other columns on the right is lower table. However, I will not pay the subscription, so I have to resign my account now. If you really wanted to help me can you please send it to me to lud088@hotmail.fr. If however you were helping me to get subscribed more than to have an answer, then thank you for your time.

>>>If you really wanted to help me can you please send it to me to lud088@hotmail.fr.

lcolgi:

1) I will not be able to send it to your email address as it is against the forum rules.

>> If however you were helping me to get subscribed more than to have an answer, then thank you for your time.

If that is what you think then I have nothing else to say :) I will however still post the solution to this thread :)

Sid

Let me know when can you be available for 15 mins to answer my questions. I still have few more :) If you can answer my questions quickly then the faster I can give you the updated workbook.

Sid

I am now, sorry to have thought about it but I think I would not be the only one to do so.
Please send me your questions, but please note that I will have to adapt the code anyway and I believe I would know how to do it, so it does not have to be very adapted to my case.

>>>but please note that I will have to adapt the code anyway and I believe I would know how to do it, so it does not have to be very adapted to my case.

Do not worry :). I will help you adapt it to your code as well if required. I am here now. Let me quickly get the questions in place. Give me 15 mins.

Sid

- by clicking on a button a listbox appear, like in a userform with two buttons "choose" and "cancel" [i know]

DONE

- this listbox's content is variable on the selection in I4, I7 and I8.
      - if I4 is W then use the data from the upper table, if not lower one (on the right, in grey)
>Upper is from R2 to R9

DONE Except the lower table. What is the exact address of the lower table?

      - depending on I7 and I8, choose the row to take the data from: here is TL and NUMERIC so take it from row 2 of upper table (R3-AG3) and display all the options (which are not '###') in the listbox.

PENDING (Here the address of the Upper table has changed? Secondly I couldn't understand the relation between "row 2 of upper table" and "TL and NUMERIC"

- the user then selects the one he wants/needs from this list and by clicking on "choose" this value should be returned in I10, with the corresponding values on the right and left of this one (same order, centered by the user).

PENDING
Returning a value to I10 is simple but couldn't understand "with the corresponding values on the right and left of this one (same order, centered by the user"

Sid

Ok so:

- Lower table is R12 -> AG22

- So the relation between the cells I7 and I8 with the lines of the UPPER table only is the following:
   - in I7 you can have TL / CY / EU / MS
   - in I8 you can have NUMERIC or ALPHA which are represented in the tables by N or A
   >> So if you have TL and NUMERIC, you should pick up the row 3 called TL/N
   >> If you have EU and ALPHA, you should pick the row 6 called EU/A

- For the lower table:
   - in I7 you can have U2 / U3 / U4 / EU / MS
   - in I8 you can have NUMERIC or ALPHA which are represented in the tables by N or A
   >> So if you have U3 and NUMERIC, you should pick up the row 15 called TL/N
   >> If you have EU and ALPHA, you should pick the row 19 called EU/A

Do not worry about whether the lines are empty or not.

- So if the user select TL and NUMERIC, then he will have to choose among 1 / 2 / ... / 9 in the list box (I don't want to display the ### if possible).

Then if he selects number 5, this value should go in I10. But 4 should go in I9, 3 in I8 as well as 6 in I11 and so on for the whole line.

Thank you

lcolgi: Please check this file. It does everything except the "Choose" Option. Let me know if it is ok till now?

Sid
MyFile.xlsm

hey nice,
ok so actually in the list box i would need one line per option, so for the line 3 (X3 to AL3) i would need the listbox to display 1, 2, 3, 4, 5, 6, 7, 8, 9. When the user choose one of these, this same one goes to I10 and the other go respectively to the other column, same line so if 5 is in I10, 4 will be in H10 and so on, same on the other side (6 in J10).

Here is the file slitghly modified.
MyFile--1-.xlsm

Updated file.

Sid
MyFile.xlsm

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.