Form Question

[dfenton21]

I have a question regarding forms in VBA. I’m almost certain this can be done, but I am still a notice.

I have a form with an input box for a staff id number. There is another hidden sheet with a list of staff names in column A, and the corresponding staff numbers in column B.

I need a macro to display another form with a list box populated by the names and numbers (sorted by name), allow the user to select one staff name, and then insert the corresponding staff number in the input box of the first form. Ideally, I would also like the user to be able to simply type in the staff number if they know it, rather then going through the “Lookup number” button.

Secondly, it is possible to validate a manual entry into that box. The entry should be a numerical value not greater than 6 digits.

Thanks in advance.

 

[dfenton21]

Can anyone help with this please?

 

[ColinKJ]

Hi dfenton21,

If you send me a private message with an email address, I'll send you over an example WB.

Or you could try the following

Code:

Private Sub ComboBox1_Change()
Cells(7, 4) = ComboBox1.Text
ComboBox1.Visible = False
End Sub

Private Sub ComboBox1_LostFocus()
ComboBox1.Visible = False
End Sub

Private Sub CommandButton1_Click()
ComboBox1.Text = ""
ComboBox1.Visible = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 And Target.Column = 4 Then
a = Cells(7, 4)
If a = "" Then GoTo endd
If Len(a) <> 6 Then Cells(7, 4) = "": GoTo endd
r = Sheets(1).Range("T65536").End(xlUp).Row
For x = 1 To r
If a = Cells(x, 20) Then GoTo endd
Next x
Cells(7, 4) = ""
End If
endd:
End Sub

ColinKJ

 

[RoryA]

If your staff list sheet is sorted, why not just use a 2 column combobox on form1? That would solve all your issues at once as far as I can see.