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
|