userform validiation

[vincent]

Hi guys, appreciate your help, i am creating a userform, and i am stuck with
this.

i have 2 drop down combo cmbName,CmbDept. when i click on cmbName , cmbDept
will show which dept the person in?

Thanks

 

[Dave Peterson]

First, I'm not sure why you'd need a combobox to display the department. I
would think that since this value is defined for each user, it would be
displayed in a Label (or even a textbox).

But even easier would be to show both fields in a single listbox -- one that has
two columns.

=====

I created a small userform (two commandbuttons, a combobox and a label). The
combobox actually has two columns in it -- but the second column is hidden from
view. But the value of that second column is still available to the code.

This was the code behind that userform:

Option Explicit
Private Sub ComboBox1_Change()
With Me.ComboBox1
Me.Label1.Caption = ""
If .ListIndex < 0 Then
'nothing chosen
Else
'second column
Me.Label1.Caption = .List(.ListIndex, 1)
End If
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
MsgBox Me.Label1.Caption
End Sub
Private Sub UserForm_Initialize()

Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
'with headers in row 1
Set myRng = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ComboBox1
.ColumnCount = myRng.Columns.Count
.ColumnWidths = "44;0" '0 hides the second column
.Style = fmStyleDropDownList
.List = myRng.Value
End With

Me.Label1.Caption = ""

End Sub