Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_Click()
Dim sh As Worksheet, rng As Range
Dim cnt As Long
if combobox1.ListIndex = -1 then
msgbox "Select a name first, then select a month"
exit sub
end if
Set sh = ActiveSheet
If sh.AutoFilterMode = True Then
If sh.FilterMode Then _
sh.ShowAllData
sh.AutoFilterMode = False
End If
Set rng = sh.Range(sh.Cells(1, 2), _
sh.Cells(1, 2).End(xlDown))
cnt = Evaluate("sumproduct(--(" & rng.Address & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

If cnt 0 Then
rng.Resize(, 3).AutoFilter Field:=2, _
Criteria1:=ComboBox1.Value
Else
rng.Resize(, 3).AutoFilter Field:=3, _
Criteria1:=ComboBox1.Value
End If
End Sub

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Its a step in the right direction but it comes up with that end/debug error
and highlights this line

rng.Resize(, 3).AutoFilter Field:=1, Criteria1:=ComboBox1.Value

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_Click()
Dim sh As Worksheet, rng As Range
Dim cnt As Long
if combobox1.ListIndex = -1 then
msgbox "Select a name first, then select a month"
exit sub
end if
Set sh = ActiveSheet
If sh.AutoFilterMode = True Then
If sh.FilterMode Then _
sh.ShowAllData
sh.AutoFilterMode = False
End If
Set rng = sh.Range(sh.Cells(1, 2), _
sh.Cells(1, 2).End(xlDown))
cnt = Evaluate("sumproduct(--(" & rng.Address & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

If cnt 0 Then
rng.Resize(, 3).AutoFilter Field:=2, _
Criteria1:=ComboBox1.Value
Else
rng.Resize(, 3).AutoFilter Field:=3, _
Criteria1:=ComboBox1.Value
End If
End Sub

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons name and
the other for the current month. What I want it to do is when they select
their name it will filter through Column C and just show the information
relating to them and if its not in this Column to then filter through Column
D (this is because there is a primary and secondary delegate in case of
abscence). With the month this looks at Column B as they have to look at
certain bits at certain times and i would also like this filtered. Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards

Its a step in the right direction
Thats cute! <g

Must be you/your layout. the code was taken from a module where it
performed as designed. You will have to tweak it to your layout.
--
Regards,
Tom Ogilvy

"Pasty" wrote in message
...
Its a step in the right direction but it comes up with that end/debug
error
and highlights this line

rng.Resize(, 3).AutoFilter Field:=1, Criteria1:=ComboBox1.Value

"Tom Ogilvy" wrote:

After rereading you post, I had second thoughts.

This should be the click event for Combobox2 (Month name)

Private Sub Combobox2_Click()
Dim sh As Worksheet, rng As Range
Dim cnt As Long
if combobox1.ListIndex = -1 then
msgbox "Select a name first, then select a month"
exit sub
end if
Set sh = ActiveSheet
If sh.AutoFilterMode = True Then
If sh.FilterMode Then _
sh.ShowAllData
sh.AutoFilterMode = False
End If
Set rng = sh.Range(sh.Cells(1, 2), _
sh.Cells(1, 2).End(xlDown))
cnt = Evaluate("sumproduct(--(" & rng.Address & "=""" & _
ComboBox2.Value & """),--(" & rng.Offset(0, 1).Address & _
"=""" & ComboBox1.Value & """))")
rng.Resize(, 3).AutoFilter Field:=1, _
Criteria1:=ComboBox2.Value

If cnt 0 Then
rng.Resize(, 3).AutoFilter Field:=2, _
Criteria1:=ComboBox1.Value
Else
rng.Resize(, 3).AutoFilter Field:=3, _
Criteria1:=ComboBox1.Value
End If
End Sub

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Where would this code go? I a module, userform or on the actual
worksheet?

"Tom Ogilvy" wrote:

Dim rng1 as Range, rng2 as Range
dim cell as Range, rng as Range
Dim bfound as Boolean

set rng = Range(cells(2,1),cells(rows.count,1).End(xlup))
for each cell in rng
if cell.Value = Combobox2.Value then
if cell.offset(0,1).Value = Combobox2.Value then
bfound = True
set rng1 = cell
exit for
end if
if cell.offset(0,2).Value = Combobox2.Value then
set rng2 = cell
end if
end if
Next
if not bFound then
if not rng2 is nothing then
set rng1 = rng2
else
MsgBox "Not found"
exit sub
end if
End if
Msgbox "Found at row: " & rng1.row

--
Regards,
Tom Ogilvy

"Pasty" wrote:

Hi,

I have set up a user form with two drop downs one for the persons
name and
the other for the current month. What I want it to do is when they
select
their name it will filter through Column C and just show the
information
relating to them and if its not in this Column to then filter
through Column
D (this is because there is a primary and secondary delegate in
case of
abscence). With the month this looks at Column B as they have to
look at
certain bits at certain times and i would also like this filtered.
Here is an
example of how it looks.

Month Of
Review Primary person Secondary person
July Charles Stewart-Jenssen Richard Anderson

How can I achieve this?

Regards