Solved: 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.

[Bob Phillips]

Why a separate form, I would put that listbox on the same form myself.

Is it okay to sort the worksheet?

[dfenton21]

It is ok to sort the sheet.

The person I'm doing the spreadsheet for wants it on a seperate form.

[Bob Phillips]

Add a button to your main form to launch the names button, I call it GetName, with this code



Private Sub GetName_Click()
With frmNames

.Show
MsgBox "Selected name = " & .lstNames.Value
End With
End Sub


then on another form, called frmNames, add a listbox, called lstNames, a textbox, called txtId, and a button called cmdOK, with this code



Option Explicit

Private Sub cmdOK_Click()
Me.Hide
End Sub


Private Sub lstNames_Click()
Me.txtId.Text = Application.Index(Worksheets("Sheet1").Columns(2), Me.lstNames.ListIndex + 2)
End Sub

Private Sub txtId_Change()
Dim RowNum As Long

On Error Resume Next
RowNum = Application.Match(Me.txtId.Text, Worksheets("Sheet1").Columns(2), 0)
If RowNum > 0 Then

Me.lstNames.ListIndex = RowNum - 2
Exit Sub
End If
RowNum = Application.Match(Val(Me.txtId.Text), Worksheets("Sheet1").Columns(2), 0)
If RowNum > 0 Then

Me.lstNames.ListIndex = RowNum - 2

End If
On Error GoTo 0
End Sub

Private Sub UserForm_Activate()
Dim cell As Range

With Worksheets("Sheet1")

.Columns("A:B").Sort key1:=.Range("A2"), order1:=xlAscending, header:=xlYes
Me.lstNames.Clear
For Each cell In .Range(.Range("A2"), .Range("A2").End(xlDown))

Me.lstNames.AddItem cell.Value
Next cell
End With
End Sub

[dfenton21]

That is fantastic. Thanks again for your help.