Userform, ComboBox, and Workbooks... Oh my!?

[fail2excel]

Userform "Customer Orderform" "ComboBox1" I would like to click the arrow,
scroll down find and click on name filling the form with the remaining
customer info that resides in an inactive workbook. The active workbook is
"WorkorderMLTestBK"
Any suggestions are greatly appreciated. Thanks Bill T.

 

[Dave Peterson]

One way:

Depending on how the data is laid out in a single sheet in that
workordermltestbk workbook, you could use =match() to find the match and
retrieve the other values.

Sub Commandbutton1_click()
dim res as variant
dim WOWks as worksheet
dim WOLookupRng as range

set wowks = workbooks("workordermltestbk").worksheets("sheet9999")

with wowks
set wolookuprng = .range("A:A")
end with

if me.combobox1.value = "" then
'nothing entered
exit sub
end if

res = application.match(me.combobox1.value, wolookuprng, 0)

if iserror(res) then
'no match, what should happen?
beep
else
me.textbox1.value = wolookuprng(res).offset(0,1) 'column B
me.textbox2.value = wolookuprng(res).offset(0,13) 'column N
...
end if

end sub

I used the "ok" button to populate the other textboxes. If you (or your users)
are like me, they could choose the wrong item a few times before selecting the
correct one.

(untested, uncompiled. Watch for typos.

 

[fail2excel]

Hi Dave.
Thanks for the quick response. My Customer data is layed out in workbook
"Customer List" A1:J1,Sheet1 are labled
First,Last,Address,City,State,Zip,Phone,Email. The Userform is in another
workbook "WorkorderMLTestbook". I don't see the macro accessing "Customer
List"
workbook. I am not sure how the how the "Match" method works but I'll read
up.
I can't even figure out how to get the combobox to reference with in the
active worksheet much less a worksheet in an inactive workbook. Thanks Bill
T.

:

 

[Dave Peterson]

If the customer is in a different workbook, then this changes:

set wowks = workbooks("workordermltestbk.xls").worksheets("sheet9999")
to something like:
set wowks = workbooks("Customer List.xls").worksheets("Sheet1")
(Yes, this Customer List workbook has to be open!)

Use excel's help to learn about the =match() function.
Or visit Debra Dalgleish's site:
http://contextures.com/xlFunctions03.html
and
http://contextures.com/xlVideos08.html#VLookup01

And if you're matching on a single last name, I hope it's unique. If you have
75 Smith's in your database, then that procedure would use the topmost matching
Smith.

And as a user, how would I know if the 47th Smith was the one I wanted.

You may want to consider using a different control. I think a Listbox that
would show more columns would be easier to pick from if I were a user. (A
combobox can show more columns in the dropdown list, but only one value will
appear in the combobox after the choice is made.)

You can hide the columns you want, but what's nice is that you can pick off the
values from those columns without returning to the worksheet.

If you want a small workbook to test, send me a private email.

Remove XSPAM from my address:
(e-mail address removed)