userform and VBA code

[jeff.white]

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!

 

[Bob Phillips]

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)

 

[jeff.white]

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)







- Show quoted text -

Not sure if an earlier reply to the above message got out there or
not...so I might be repeating myself ....

I was thinking something easier....! I have two fields on my
userform, for now. A combobox1 and a textbox1. The properties for
the Combobox1 has a 'rowsource' of a2:a1600'. This works fine. What
I like Textbox1 do is, when an employee number is selected, say 5375
from cell A34, the textbox1 shows what is in cell B34....which is the
name of the employee....any ideas with this? Thanks for the quick
reply...!!!

 

[Bob Phillips]

Then that is just

Dim iRow as long

On Error Resume Next
iRow =
Application.Match(Combobox1.Value,Worksheets("Data").Columns(1),0)
On Error Goto 0
If iRow > 0 Then
TextBox1.Text = Worksheets("Data").Cells(iRow,"B").Value
End If

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jeff.white]

Then that is just

Dim iRow as long

On Error Resume Next
iRow =
Application.Match(Combobox1.Value,Worksheets("Data").Columns(1),0)
On Error Goto 0
If iRow > 0 Then
TextBox1.Text = Worksheets("Data").Cells(iRow,"B").Value
End If

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

I've tried your suggestion Bill, thanks but I get nothing returned to
textbox1. I've changed the sheet1 to n11, but I still get no data in
the textbox, no errors either?

Private Sub ComboBox1_Change()
Dim iRow As Long
On Error Resume Next
iRow = Application.Match(ComboBox1.Value,
Worksheets("n11").Columns(1), 0)
On Error GoTo 0
If iRow > 0 Then
TextBox1.Text = Worksheets("n11").Cells(iRow, "B").Value
End If
End Sub

 

[jeff.white]

I've tried your suggestion Bill, thanks but I get nothing returned to
textbox1. I've changed the sheet1 to n11, but I still get no data in
the textbox, no errors either?

Private Sub ComboBox1_Change()
Dim iRow As Long
On Error Resume Next
iRow = Application.Match(ComboBox1.Value,
Worksheets("n11").Columns(1), 0)
On Error GoTo 0
If iRow > 0 Then
TextBox1.Text = Worksheets("n11").Cells(iRow, "B").Value
End If
End Sub- Hide quoted text -

- Show quoted text -

Sorry, I mean to say thanks Bob!

 

[Bob Phillips]

That suggests it isn't finding the value in column A.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)