Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter sub
Sorry, the Cancel button code should be
Private Sub Cancel_Click() Owner = "" Userform1.Hide End Sub Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
How to set multiple filter values for a filter in the page fields | Excel Programming |