Filter sub
 

I am using this code to automatically filter for the information they are
after when they go in to my risk register and it works. Problem is it may be
a secondary person who is updating the register and I need to make it filter
the column next to it as well. Is there an easy way to do this?

Sub FilterRiskOwner()


strRiskOwner = InputBox("Please enter your full name")
If strRiskOwner = "" Then
Exit Sub
End If

Sheets("Risk By Function").Select
Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner

End Sub

Filter sub
 

Hi
Just add another Filter. I'd also suggest starting by turning off
AutoFilter just incase it has been left on by a previous user.
regards
Paul
Sub FilterRiskOwner()

strRiskOwner = InputBox("Please enter your full name")
If strRiskOwner = "" Then
Exit Sub
End If
With Sheets("Risk By Function")
.select
.AutoFilterMode = False 'Removes drop down arrows
.AutoFilter Field:=3, Criteria1:=strRiskOwner
.AutoFilter Field:=4, Criteria1:=NextOne
End With
End Sub

With FixedHeadingsRange.Parent
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Case 2
SurnamesRange.Offset(0, 2).AutoFilter Field:=1,
Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
Criteria2:=CStr(Grouplistdata(2))

On Mar 7, 9:55 am, Pasty wrote:
I am using this code to automatically filter for the information they are
after when they go in to my risk register and it works. Problem is it may be
a secondary person who is updating the register and I need to make it filter
the column next to it as well. Is there an easy way to do this?

Sub FilterRiskOwner()

strRiskOwner = InputBox("Please enter your full name")
If strRiskOwner = "" Then
Exit Sub
End If

Sheets("Risk By Function").Select
Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner

End Sub

Filter sub
 

This is along the lines but seems to look in both columns rather than one
followed by the other so brings up nothing when filtered.

" wrote:

Hi
Just add another Filter. I'd also suggest starting by turning off
AutoFilter just incase it has been left on by a previous user.
regards
Paul
Sub FilterRiskOwner()

strRiskOwner = InputBox("Please enter your full name")
If strRiskOwner = "" Then
Exit Sub
End If
With Sheets("Risk By Function")
.select
.AutoFilterMode = False 'Removes drop down arrows
.AutoFilter Field:=3, Criteria1:=strRiskOwner
.AutoFilter Field:=4, Criteria1:=NextOne
End With
End Sub

With FixedHeadingsRange.Parent
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Case 2
SurnamesRange.Offset(0, 2).AutoFilter Field:=1,
Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
Criteria2:=CStr(Grouplistdata(2))

On Mar 7, 9:55 am, Pasty wrote:
I am using this code to automatically filter for the information they are
after when they go in to my risk register and it works. Problem is it may be
a secondary person who is updating the register and I need to make it filter
the column next to it as well. Is there an easy way to do this?

Sub FilterRiskOwner()

strRiskOwner = InputBox("Please enter your full name")
If strRiskOwner = "" Then
Exit Sub
End If

Sheets("Risk By Function").Select
Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner

End Sub

Filter sub
 

Hi
Had a typo and forgot the Range("a1") bit;
..Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner
..Range("A1").AutoFilter Field:=4, Criteria1:=NextOne

It filters in one column, then filters those by the criterion in the
second column. This will give nothing if if there is no row that meets
both criteria, which shouldn't happen if both criteria exist in your
columns?
regards
Paul

Filter sub
 

I see its my fault for not explaining it clearly. There is one column which
has all the primary people carrying out assurance and a column next to it
with their delegates (in case they aren't in) I want it to pop up and ask for
their name and if they are a primary person search one column and if
secondary search the other one but was trying to think of a smarter way of
doing it rather than just have them choose whether primary or secondary and
then search on their name.

" wrote:

Hi
Had a typo and forgot the Range("a1") bit;
..Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner
..Range("A1").AutoFilter Field:=4, Criteria1:=NextOne

It filters in one column, then filters those by the criterion in the
second column. This will give nothing if if there is no row that meets
both criteria, which shouldn't happen if both criteria exist in your
columns?
regards
Paul

Filter sub
 

Hi
You will need a userform rather than an inputbox to be safe - what if
someone types something in capitals for example?
STEPS:
On a blank sheet, I'll call it TEST, put in the names and status.
I'll assume you have headings and are starting in Cell A1

Name Status
john Primary
mary Primary
Peter Delegate

So the data is in A2 to B4. You can hide this sheet.
In the Visual Basic Editor create a userform (UserForm1) with a
combobox (Combobox1) on it, a Cancel (Change name of Commandbutton to
Cancel) button and an OK (change name of commandbutton to OK) button.
For the combobox set these properties in the Properties window for the
combobox
Columncount 2
ColumnHeads True
ColumnWidths ;0 pt
RowSource TEST!$A$2:$B$4 (change as required)

Double click the OK button and insert this code

Dim Status as String, Owner as String
Private Sub OK_Click()
With UserForm1.Combobox1
Owner = .List(.ListIndex, 0)
Status = .List(.ListIndex, 1)
End With
Userform1.Hide
End Sub

Double click the Cancel button and insert this code
Private Sub Cancel_Click()
Owner = ""
Unload Userform1
End Sub

Now change your filter code to this:

Sub FilterRiskOwner()
strRiskOwnerStatus = Userform.Status
strRiskOwner = Userform.Owner
Unload UserForm1
If strRiskOwner = "" Then
Exit Sub
End If
With Sheets("Risk By Function")
.select
.AutoFilterMode = False 'Removes drop down arrows
If strRiskOwnerStatus = "Primary" then
.Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner
Else
.Range("A1").AutoFilter Field:=4, Criteria1:=strRiskOwner
End With
End Sub

regards
Paul

On Mar 7, 1:24 pm, Pasty wrote:
I see its my fault for not explaining it clearly. There is one column which
has all the primary people carrying out assurance and a column next to it
with their delegates (in case they aren't in) I want it to pop up and ask for
their name and if they are a primary person search one column and if
secondary search the other one but was trying to think of a smarter way of
doing it rather than just have them choose whether primary or secondary and
then search on their name.



" wrote:
Hi
Had a typo and forgot the Range("a1") bit;
..Range("A1").AutoFilter Field:=3, Criteria1:=strRiskOwner
..Range("A1").AutoFilter Field:=4, Criteria1:=NextOne

It filters in one column, then filters those by the criterion in the
second column. This will give nothing if if there is no row that meets
both criteria, which shouldn't happen if both criteria exist in your
columns?
regards
Paul- Hide quoted text -

- Show quoted text -

Filter sub
 

Sorry, the Cancel button code should be
Private Sub Cancel_Click()
Owner = ""
Userform1.Hide
End Sub

Paul