userform and VBA code
I would suggest that you get all employee ID and load these into a combobox.
When a selection is made from there, build an array of records for that
employee and provide back and forward buttons for going through this array,
and retreive those details and display them.
You can get all unique employees like so
With Worksheets("Data")
iLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
sId = ""
For i = 2 To iLastRow
if .Cells(i,"A").Value < sId Then
sId = .Cells(i,"A").Value
Me.ComboBox1.AddItem .Cells(i,"A").Value
End If
Next I
Me.ComboBox1.ListIndex = -1
End With
You can then load an array like this, and prime it like so
ReDim aryRows(1 To 1)
iRow = 1
With Worksheets("Data")
For i = 2 To iLastRow
If .Cells(i, "A").Value = 1 Then 'Me.Combobox1.Value Then
ReDim Preserve aryRows(1 To iRow)
aryRows(iRow) = i
iRow = iRow + 1
End If
Next i
End With
iRow = 0
and then retrieve like so
Private Sub cmdNext_Click()
With Worksheets("Data")
If iRow = UBound(aryRows) Then
MsgBox "no more"
Else
iRow = iRow + 1
Me.TextBox1.Text = .Cells(iRow, "A").Value 'emp id
Me.TextBox1.Text = .Cells(iRow, "B").Value 'name
'etc.
End If
End With
End Sub
Private Sub cmdPrevious_Click()
With Worksheets("Data")
If iRow <=1 Then
MsgBox "no more"
Else
iRow = iRow - 1
Me.TextBox1.Text = .Cells(iRow, "A").Value 'emp id
Me.TextBox1.Text = .Cells(iRow, "B").Value 'name
'etc.
End If
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
wrote in message
ups.com...
I'm starting to build a user form in excel and need a push start. I
have an excel file that contains about 15 columns of data with about
1600 rows. The table is a list of all active employees, their
location, department ID, their RC, superviors's name...etc. I'd like
to create a userform that can be used to list a given employee and all
the items from their row of data.
So to get me started can someone show me an example of vba code that
will be used in a text box, for example, as a search...ie the user
enters an employee ID number (which is in column A) and then other
text boxes brings in the location, rc and so on in other text boxes
placed on the form. If I can see some example code to pull in the
value from Column A and then Column B that would be a great start for
me. Thanks a ton!
|