listbox selection based on underlying cell value

Hi all. I have a userform to enter data consisting of batch no, category, variety and supplier.

Here is some sample inputs

Batch Category Variety Supplier
01 food chips macdonalds
02 drink coke own brand
03 dessert pie mr kipling
04 food burger burger king
05 drink milkshake macdonalds
etc etc

the batch input in userform is a text box.
All the rest are list boxes.
I populate them on initalisation with rowsources with listindex set at -1.

What i want to do is after i type in the batch no. the listboxes populate the current selection i.e.. if i entered 03 in batch text box dessert, pie and mr kipling would all be selected in respective listboxes.

is this possible.

Hope explained it ok.

This is simple to do but why are you using three listboxes? Or even listboxes. If you are only showing one item.

I only use listboxes to display multiple options and to select one of them.

If you are not selecting then use a multicolumn listbox

Hi I have used a listbox cos it was my understanding that unlike comboboxes it stopped any entries other that what was in the range been inputted. I should have said that want them as listbox so can amend the info if I want to. This userform will be used as an amend option so say I accidentally chose chips instead of burgers for batch 4. I would type in 4 then let it populate. tab to variety and amend chips to burgers. Press enter at bottom of form that would enter new data.

Hope that makes more sense

Ok Let me explain the process to you.

when you change the batch number you need to write the textbox change event.

use find to locate your batch code in your database. store the row as a variable

now use the cell function to update your listboxes.

Eg

Listbox1.clear
Listbox2.clear
Listbox3.clear

Listbox1.additem cells(SR,2).value
Listbox2.additem cells(SR,3).value
Listbox3.additem cells(SR,4).value

Ok Chris.

i would not use lstboxes. Textboxes work just as well.

You can use the textbox chage event to ammend the entry if required.

I always declare a public variable "Changeflag" as an integer

at the start of each peice of code check if the flag is 1 if so then quit
otherwise set it to one.

Do your actions

set the flag to 0

eg:

textbox1 change_event()
if changeflag = 1 then exit sub
changeflag 1 = 1
textbox2.text = "Two"
textbox3.text = "Three"
Textbox4.text = "Four"
changeflag = 0

end sub

textbox2 change_event()
if changeflag = 1 then exit sub
changeflag 1 = 1
textbox2.text = "Twelve"
textbox3.text = "Thirteen"
Textbox4.text = "Fourteen"
changeflag = 0

end sub

Hi thanks for your responses so far. Trying to upload my worksheet but comp appears to be on last legs and is not responding.

My prob with text boxes is that if I want to change the data once it appears in the userform then the users could put anything down rather than being locked into a selection of variety (which contains over 100 pre defined options). currently using this code to do the batch lookup function and complete the one additional text box I have but again not working for listboxes:

Please Login or Register  to view this content.