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

What is your code to load the combobox ??

"Patrick C. Simonds" wrote:

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

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.


"Mike" wrote in message
...
What is your code to load the combobox ??

"Patrick C. Simonds" wrote:

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

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.
"Patrick C. Simonds" wrote in message
...
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.


"Mike" wrote in message
...
What is your code to load the combobox ??

"Patrick C. Simonds" wrote:

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

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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" wrote:

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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.

--
Rick (MVP - Excel)


"ordnance1" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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

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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

Sorry I had missed the . between rick and news


"Rick Rothstein" wrote in message
...
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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Sorry I had missed the . between rick and news


"Rick Rothstein" wrote in message
...
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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Sorry I had missed the . between rick and news


"Rick Rothstein" wrote in message
...
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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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 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.



"ordnance1" wrote:

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Sorry I had missed the . between rick and news


"Rick Rothstein" wrote in message
...
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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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

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

--
Rick (MVP - Excel)


"Pats" wrote in message
...
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.



"ordnance1" wrote:

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Sorry I had missed the . between rick and news


"Rick Rothstein" wrote in
message
...
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).

--
Rick (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
Thanks Rick it should be in your e-mail.


"Rick Rothstein" wrote in
message
...
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 (MVP - Excel)


"Patrick C. Simonds" wrote in message
...
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