vlookup combo box

[Charles L. Snyder]

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]

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
.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]

Thanks !

That works perfectly.

CLS