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

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

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

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

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

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

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

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

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

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

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

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

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

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.