Solved: List box value from an active cell.

[pcarmour]

Hi, I have a user form with 3 list boxes, in List box 1 I want to show the value from the active cell on the related spread sheet in List box 2 I want to show the value from cell offset C2 to the active cell and in List box 3 I want to show the value from cell offset C3 to the active cell. I have tried to use various row source VBA codes but have failed to get it right.
Any help would be much appreciated.
I am working with Windows Home Premium version 6.1.7601 SP 1 Build7601and Excel version 14.0.6123.5001 (32 bit)

[CodeNinja]

Please post example of workbook.

[pcarmour]

Hi, Thanks for looking at this.
I have attached a reduced sample of my worksheet.
When a user wants to add an order he selects the row he is adding to and then clicks the Add Order button which opens the user form.
On this form I want to show the Name B/S and Code of the row selected in list boxes 1,2 and 3.
The active cell is the name selected in Column C.

[BrianMH]

Your attachment doesn't have any userform in it.

[pcarmour]

Sorry, here is the correct version.

[snb]

Use the list property to populate a combobox/listbox

[BrianMH]

Here you are. I also updated your initial code so that if someone isn't clicking column C it gives them a warning since you said they need to be clicking column C. By the way the activecell is no longer in column 3 once your code runs so the offset is from the first column.

[pcarmour]

Hi BrianMH,
Thank you very much, working as required.

[pcarmour]

Use the list property to populate a combobox/listbox

Hi SNB,
Can you please explain in more detail as I would like to use the list property but have failed to use it correctly,
Regards,
Peter

[BrianMH]

Do you actually need a listbox? It looks like you are using them as textboxes anyway.

[snb]

see the attachment

[pcarmour]

Hi BrianMH,
I couldn't see that a textbox would load with info from the spread sheet so using a listbox was my option.

[pcarmour]

Hi SNB,

Thank you for your amended user form.
I will get back once I have had a chance to look at it, sorry for any delay in answering

[BrianMH]

Ah I see. Well you can read the spreadsheet with VBA and then assign the text box values.

[pcarmour]

Thanks BrianMH, I will give it a try.

[pcarmour]

Hi SNB.
Yes I can now see what you mean, much better code, thank you.