Listbox on Excel Userform

[jeff.white]

I have a an excel sheet with about 1000 rows of data, employee related
data, ID, Name, Department, Location...etc. I've gotten some help
here with a combobox and textboxes, which was great! Now I'd like to
expand on my user form by adding another combobox that will list the
Department IDs. When a given department is selected in the Combobox I
was thinking a listbox could be used to list all employees in that
department, with some of the particulars, like Full Time or Part time,
Hire Date...etc.

Can someone provide me with some sample code that might help me, a vba
newbie, a start?

 

[Dave Peterson]

It sounds to me like you're creating your own Data|Filter|autofilter.

You may want to use the built-in tools that excel offers. It really makes life
easier.

 

[jeff.white]

It sounds to me like you're creating your own Data|Filter|autofilter.

You may want to use the built-in tools that excel offers. It really makes life
easier.

Thanks Dave, but wouldn't that just take the fun out of creating a
userform?

Actually, I have a userform now that one can search for an employee,
either by employee id or by name. One of the items returned is the
department ID number. Is it possible that once an employee is listed
that one can click in or select the textbox that holds the department
ID, that the ListBox then lists all other employees names that are in
that department? Does that make sense?

 

[Dave Peterson]

You could always get your input (name/id) and then you could always just loop
through the range (I'm assuming your data is kept on a worksheet).

Look for the match and plop the info from each match into that listbox.

This may give you some ideas--how to add the items to the listbox and how to add
it as a formatted value:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim myRng As Range
Dim myCell As Range

If Trim(Me.TextBox1.Value) = "" Then
Beep
End If

With Worksheets("sheet1")
'column c contains the dept for me
'row 1 has headers
Set myRng = .Range("c2", .Cells(.Rows.Count, "c").End(xlUp))
End With


With Me.ListBox1
.Clear
For Each myCell In myRng.Cells
'convert dept number to a long--you may not need this
If myCell.Value = CLng(Me.TextBox1.Value) Then
.AddItem myCell.Offset(0, 1).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 3).Value
.List(.ListCount - 1, 2) _
= Format(myCell.Offset(0, 8).Value, "mm/dd/yyyy")
End If
Next myCell
End With

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 3 'whatever you want
.MultiSelect = fmMultiSelectMulti 'maybe?
End With
End Sub

 

[jeff.white]

You could always get your input (name/id) and then you could always just loop
through the range (I'm assuming your data is kept on a worksheet).

Look for the match and plop the info from each match into that listbox.

This may give you some ideas--how to add the items to the listbox and how to add
it as a formatted value:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim myRng As Range
Dim myCell As Range

If Trim(Me.TextBox1.Value) = "" Then
Beep
End If

With Worksheets("sheet1")
'column c contains the dept for me
'row 1 has headers
Set myRng = .Range("c2", .Cells(.Rows.Count, "c").End(xlUp))
End With

With Me.ListBox1
.Clear
For Each myCell In myRng.Cells
'convert dept number to a long--you may not need this
If myCell.Value = CLng(Me.TextBox1.Value) Then
.AddItem myCell.Offset(0, 1).Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 3).Value
.List(.ListCount - 1, 2) _
= Format(myCell.Offset(0, 8).Value, "mm/dd/yyyy")
End If
Next myCell
End With

End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 3 'whatever you want
.MultiSelect = fmMultiSelectMulti 'maybe?
End With
End Sub









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave....Thanks...Perfect.....So much more fun that Autofilters!!
haha....I think I should be able to use this for multible command
buttons, for example, show all employees that are under a certain
supervisor, have the same job title ... thanks, I was able to make
only a few minor tweaks to pull in the column I wanted to see in the
list box. I might play with the width of each item, right now there
is too much space between columns....Thanks again.

 

[jeff.white]

Dave....Thanks...Perfect.....So much more fun that Autofilters!!
haha....I think I should be able to use this for multible command
buttons, for example, show all employees that are under a certain
supervisor, have the same job title ... thanks, I was able to make
only a few minor tweaks to pull in the column I wanted to see in the
list box. I might play with the width of each item, right now there
is too much space between columns....Thanks again.- Hide quoted text -

- Show quoted text -

Dave....Thanks again for your help...I've made some tweaks and added a
couple of buttons to my user form. One item I'm pulling into the
listbox is Salary. A wish list I have would be to add 3 textboxes to
the userform. One showing the Highest Salary found in the List box,
one showing the lowest and the 3rd showing the average salary. One of
the 'searches' are done by job title. So what I'm trying to show is
when I search a given job title that I can show high, low and average
of that position. Can this be done from what is shown in the listbox?