Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
I'm using Stephen Bullen's Function code to populate my Cell C38:
After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Change the ShowAll macro to clear it
Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
With the function I wrote, I use something like this
http://tinyurl.com/2dzpou see at the bottom of the post. -- regards, Tom Ogilvy "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Bob:
Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Thank you, thank you, thank you, Computers... they do make it too easy to say THANK YOU !! Love you Bob, "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Oopps,,, Bob --this destroys (deletes) my formula
=FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter.. Any other thought? Jim "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
shaking head
-- Regards, Tom Ogilvy "JMay" wrote in message ... Oopps,,, Bob --this destroys (deletes) my formula =FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter.. Any other thought? Jim "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Sub ShowAll()
Dim tmp On Error GoTo away With ActiveSheet If .FilterMode Then .ShowAllData tmp = .Range("C38").Formula .Range("C38").Value = "" .Range("C38").Formula = tmp End If End With away: End Sub -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JMay" wrote in message ... Oopps,,, Bob --this destroys (deletes) my formula =FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter.. Any other thought? Jim "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
That doesn't work if he is cancelling the filter with his ShowAll macro.
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... With the function I wrote, I use something like this http://tinyurl.com/2dzpou see at the bottom of the post. -- regards, Tom Ogilvy "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Sub ShowAll()
On Error GoTo away ActiveSheet.ShowAllData Range("C38").Calculate away: End Sub might be more consistent particularly if you have any change events. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Sub ShowAll() Dim tmp On Error GoTo away With ActiveSheet If .FilterMode Then .ShowAllData tmp = .Range("C38").Formula .Range("C38").Value = "" .Range("C38").Formula = tmp End If End With away: End Sub -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JMay" wrote in message ... Oopps,,, Bob --this destroys (deletes) my formula =FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter.. Any other thought? Jim "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Useful nontheless.
Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData Range("C38").Calculate away: End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... That doesn't work if he is cancelling the filter with his ShowAll macro. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... With the function I wrote, I use something like this http://tinyurl.com/2dzpou see at the bottom of the post. -- regards, Tom Ogilvy "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
That Nailed it!!
Tks again. Jim "Bob Phillips" wrote in message : Sub ShowAll() Dim tmp On Error GoTo away With ActiveSheet If .FilterMode Then .ShowAllData tmp = .Range("C38").Formula .Range("C38").Value = "" .Range("C38").Formula = tmp End If End With away: End Sub -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "JMay" wrote in message ... Oopps,,, Bob --this destroys (deletes) my formula =FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter.. Any other thought? Jim "Bob Phillips" wrote in message : Change the ShowAll macro to clear it Sub ShowAll() On Error GoTo away With ActiveSheet .ShowAllData .Range("C38").ClearContents End With away: End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Not saying it isn't, just doesn't help Jim.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Tom Ogilvy" wrote in message ... Useful nontheless. Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData Range("C38").Calculate away: End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... That doesn't work if he is cancelling the filter with his ShowAll macro. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... With the function I wrote, I use something like this http://tinyurl.com/2dzpou see at the bottom of the post. -- regards, Tom Ogilvy "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
Sure it does. Maybe not for the problem he complained of. But I also
provided help for that. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Not saying it isn't, just doesn't help Jim. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Tom Ogilvy" wrote in message ... Useful nontheless. Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData Range("C38").Calculate away: End Sub -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... That doesn't work if he is cancelling the filter with his ShowAll macro. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom Ogilvy" wrote in message ... With the function I wrote, I use something like this http://tinyurl.com/2dzpou see at the bottom of the post. -- regards, Tom Ogilvy "JMay" wrote in message ... I'm using Stephen Bullen's Function code to populate my Cell C38: After I Filter my C38 it Displays LGC as it should; I have a forms button assigned (also below) ShowAll When I click the button all records are displayed, but my Cell C38 still Shows the LGC Note that I did add (Line3) Application.Volatile, but It didn't help so I commented it out. What can I do to have C38 Display a blank - as it does if I click on the Column header down-arrow and select ALL,, When C38 does away with the LGC and Displays Blank << What I want to happen. TIA, Jim May Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'Application.Volatile Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function Sub ShowAll() On Error GoTo away ActiveSheet.ShowAllData away: End Sub |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function not completely volatile help
"Tom Ogilvy" wrote in message
... Sure it does. Maybe not for the problem he complained of. Exactly! But I also provided help for that. Not originally. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Today - volatile function | Excel Discussion (Misc queries) | |||
is MATCH a volatile function? | Excel Discussion (Misc queries) | |||
Volatile User Defined Function?? | Excel Programming | |||
why is the INDIRECT function volatile? | Excel Worksheet Functions | |||
Making a function somewhat volatile. | Excel Programming |