ComboBox problem

[Patrick C. Simonds]

I have a list of 200+ employees and they are divided between 5 workgroups. I
have a UserForm with a ComboBox which allows one to chose an employees name.
The problem is now the requirement is that if I am working on a worksheet
for one group, that only those people show in the ComboBox list of
employees. So I created an autofilter routine that compressed the list of
employees depending on which worksheet was being worked on. The problem is
that even after the autofilter routine runs, when the the UserForm is shown
and you click on the drop down arrow for the ComboBox, all employees still
show up. I was really hoping to not have to maintain multiple lists of
employees

 

[Mike]

What is your code to load the combobox ??

 

[Patrick C. Simonds]

This code loads the UserForm that the code resides on (I have also created a
UserForm for each of the 4 other Employee Groups):

If Not Application.Intersect(Target, Range("C7:C1926")) Is Nothing Then
ReliefBoardHours.Show


And this is the RowSource for the ComboBox:

Employees


Emplyees is a defined name on the Employees worksheet.

 

[Patrick C. Simonds]

This code loads the UserForm that the ComboBox resides on (I have also
created a
UserForm for each of the 4 other Employee Groups):

If Not Application.Intersect(Target, Range("C7:C1926")) Is Nothing Then
ReliefBoardHours.Show


And this is the RowSource for the ComboBox:

Employees


Emplyees is a defined name on the Employees worksheet.

 

[Rick Rothstein]

You didn't tell us very much about your setup, so I had to guess. I put the
critical stuff in Const statements. I'm thinking there is enough here for
you to be able to modify it to fit your needs...

Dim X As Long
Dim LastRow As Long
Dim GroupToFind As String

Const DataSheet As String = "Sheet4"
Const NameColumn As Long = 1
Const GroupColumn As Long = 2

GroupToFind = InputBox("Which group number?")

With Worksheets(DataSheet)
LastRow = .Cells(.Rows.Count, NameColumn).End(xlUp).Row
ComboBox1.Clear
For X = 2 To LastRow
If .Cells(X, GroupColumn).Value = GroupToFind Then
ComboBox1.AddItem .Cells(X, NameColumn).Value
End If
Next
End With

 

[ordnance1]

Just to add a bit more information.

I have a worksheet for each workgroup and each worksheet calls it's own
UserForm designed for that workgroup.

Then there is a worksheet with the employee list. All employee names are
listed in column A which Column B - G has the hours they work for each day
of the week and column I has the Workgroup they are assigned to.

So what I was hoping was that if I was on the Relief Board worksheet and the
Relief Board Userform opened, then the ComboBox1 on the UserForm would show
only those people in the relief Board workgroup. And then the same would hold
true for all other UserForms ( if I was on the Driver worksheet then
comboBox1 on the Driver UserForm would show only names in the Drivers
Workgroup.)

My attempted solution was to autofilter the list of employees and that work
fine. If the Relief Board UserForm opened the list of employees filtered down
to just Relief Board employees. But ComboBox1 still showed all employee names.

 

[Rick Rothstein]

You don't have to filter your worksheet, unless you want to for other
reasons... the code below does not require it. You would use this Initialize
event code for each of your UserForms...

Private Sub UserForm_Initialize()
Dim X As Long
Dim LastRow As Long

Const DataSheet As String = "Sheet1"
Const GroupToFind As String = "Relief Board"
Const NameColumn As String = "A"
Const GroupColumn As String = "I"

With Worksheets(DataSheet)
LastRow = .Cells(.Rows.Count, NameColumn).End(xlUp).Row
ComboBox1.Clear
For X = 2 To LastRow
If .Cells(X, GroupColumn).Value = GroupToFind Then
ComboBox1.AddItem .Cells(X, NameColumn).Value
End If
Next
End With
End Sub

Just change the Const statement for the DataSheet to reflect the name of the
worksheet where all your data is stored; and change the Const statement for
the GroupToFind to the appropriate group name for the UserForm its code is
on. Note that I am assuming the ComboBox on each UserForm is named ComboBox1
in my code.

 

[Patrick C. Simonds]

I get a runtime error "unspecified error" but of course it does not tell
me what caused the error.

I modified the code as you suggested (see code below). The employee list is
located on the Employee List worksheet. The RB in this case refers to the
Relief Board, column I is where that abbreviation is located.


Private Sub UserForm_Initialize()

Dim X As Long
Dim LastRow As Long

Const DataSheet As String = "Employee List"
Const GroupToFind As String = "RB"
Const NameColumn As String = "A"
Const GroupColumn As String = "I"

With Worksheets(DataSheet)
LastRow = .Cells(.Rows.Count, NameColumn).End(xlUp).Row
ComboBox1.Clear
For X = 2 To LastRow
If .Cells(X, GroupColumn).Value = GroupToFind Then
ComboBox1.AddItem .Cells(X, NameColumn).Value
End If
Next
End With

End Sub

 

[Rick Rothstein]

I'm not sure exactly what to tell you... I tested the code before I posted
it, and I just tested the code as you modified it, and in both cases the
code worked as intended.

Why don't you send me your Excel file so that I can look at exactly what
your setup is... just remove the NO.SPAM parts from my email address.

 

[Patrick C. Simonds]

Thanks Rick it should be in your e-mail.

I'm not sure exactly what to tell you... I tested the code before I posted
it, and I just tested the code as you modified it, and in both cases the
code worked as intended.

Why don't you send me your Excel file so that I can look at exactly what
your setup is... just remove the NO.SPAM parts from my email address.

 

[Rick Rothstein]

It isn't there yet (15+ minutes now after you posted your message). You
should have sent it to this address...

rick(dot)news(at)verizon(dot)net

where you would replace "(dot)" with a "." and "(at)" with "@" (without the
quote marks, of course).

 

[Patrick C. Simonds]

Sorry I had missed the . between rick and news

 

[Rick Rothstein]

I got it. I'm going out for a little while so I'll look at it this evening
and get back to you.

 

[ordnance1]

Thanks rick

Just a not I did find that I was able to get past the unspecifed error by
removing the ComboBox1.clear line, but now I get a permission error.

 

[Pats]

Rick thanks for your time. I now have everything up and running. The
permission error was caused by the fact that I had a RowSouce defined for the
ComboBox. Once I removed that it was all good.

Again thanks for yor time.

 

[Rick Rothstein]

You are welcome... I'm glad you got it working.