Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform, ComboBox, and Workbooks... Oh my!?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform, ComboBox, and Workbooks... Oh my!?
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("sheet99 99") 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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Userform, ComboBox, and Workbooks... Oh my!?
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" wrote: "Dave Peterson" wrote: 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("sheet99 99") 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with a ComboBox on a UserForm | Excel Discussion (Misc queries) | |||
ComboBox on a UserForm | Excel Worksheet Functions | |||
Yet another userform combobox question | Excel Discussion (Misc queries) | |||
Userform combobox question | Excel Discussion (Misc queries) | |||
Userform w/ComboBox | Excel Discussion (Misc queries) |