View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default 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!