Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup combo box
Hi
I am using office 2003 excel. I have a userform with a combo box which uses another ws (AutoEntry! a1:a20) to populate the combo box. When an entry is selected, I use a case conditional (see below) to assign values to variables (cpt, icd9, etc), and then fill in the primary ws. Obviously, using case statements is stupid - I want to have a 3 or 4 column, several hundred row ws from which the first column populates the drop down combo box, and VLOOKUP is used to get the associated (cpt, icd9, etc) variable values, but I'm stuck viz how to do this. Function FxEnter(my_select) LastMTcell Select Case my_select Case Is = "Circumcision, FH" icd9 = "605" cpt = "54161" cpt2 = "" Case Is = "Ing Hernia Uni < 6mo" icd9 = "550.90" cpt = "49495" cpt2 = "49320" Case Is = "Ing Hernia Bil < 6mo" icd9 = "550.92" cpt = "4949550" cpt2 = "49320" Case Is = "HPS Lap" icd9 = "750.5" cpt = "43659" cpt2 = "49320" Case Is = "Meatotomy" icd9 = "598.8" cpt = "53020" End Select ActiveCell.FormulaR1C1 = "Snyder, Charles L." ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = Format(Now, "mm/dd/yyyy") ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = MedRec ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = icd9 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = dx2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = dx3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "CMH" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" UserForm1.Hide TIA clsnyder |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup combo box
One way you could use is to use =vlookup() to match on that column and pick out
the other 2 (or 3 values). Another way is to pick up the whole range (3 or 4 columns), but only show the first column. But those hidden values can still be retrieved. I chose the second option: Option Explicit Private Sub CommandButton1_Click() Dim iCtr As Long If Me.ComboBox1.ListIndex < 0 Then Beep Exit Sub End If With Me.ComboBox1 For iCtr = 1 To .ColumnCount MsgBox .List(.ListIndex, iCtr - 1) Next iCtr End With End Sub Private Sub UserForm_Initialize() Dim myArr As Variant With Me.ComboBox1 .ColumnCount = 4 'columns A:D .ColumnWidths = "22;0;0;0" 'hide the last 3 columns End With With Worksheets("autoentry") 'headers in row 1? myArr = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Resize(, Me.ComboBox1.ColumnCount) End With Me.ComboBox1.List = myArr End Sub I'm not sure if it's important to hide this kind of stuff either. You may want to consider keeping it showing. And if you want the user to always see the options, you could use a (wide!) listbox, instead. "Charles L. Snyder" wrote: Hi I am using office 2003 excel. I have a userform with a combo box which uses another ws (AutoEntry! a1:a20) to populate the combo box. When an entry is selected, I use a case conditional (see below) to assign values to variables (cpt, icd9, etc), and then fill in the primary ws. Obviously, using case statements is stupid - I want to have a 3 or 4 column, several hundred row ws from which the first column populates the drop down combo box, and VLOOKUP is used to get the associated (cpt, icd9, etc) variable values, but I'm stuck viz how to do this. Function FxEnter(my_select) LastMTcell Select Case my_select Case Is = "Circumcision, FH" icd9 = "605" cpt = "54161" cpt2 = "" Case Is = "Ing Hernia Uni < 6mo" icd9 = "550.90" cpt = "49495" cpt2 = "49320" Case Is = "Ing Hernia Bil < 6mo" icd9 = "550.92" cpt = "4949550" cpt2 = "49320" Case Is = "HPS Lap" icd9 = "750.5" cpt = "43659" cpt2 = "49320" Case Is = "Meatotomy" icd9 = "598.8" cpt = "53020" End Select ActiveCell.FormulaR1C1 = "Snyder, Charles L." ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = Format(Now, "mm/dd/yyyy") ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = MedRec ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = icd9 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = dx2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = dx3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = cpt3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "CMH" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "No" UserForm1.Hide TIA clsnyder -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup combo box
Thanks !
That works perfectly. CLS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using vlookup with combo box | Excel Worksheet Functions | |||
Combo Box and VLOOKUP | Excel Discussion (Misc queries) | |||
Combo Box / VLOOKUP | Excel Programming | |||
vlookup from a combo box? | Excel Discussion (Misc queries) | |||
Vlookup from Combo Box | Excel Discussion (Misc queries) |