Function not completely volatile help

[JMay]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

With the function I wrote, I use something like this

http://tinyurl.com/2dzpou

see at the bottom of the post.

 

[JMay]

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,

 

[JMay]

Oopps,,, Bob --this destroys (deletes) my formula

=FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter..

Any other thought?

Jim

 

[Tom Ogilvy]

shaking head

--
Regards,
Tom Ogilvy

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]

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)

 

[Bob Phillips]

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]

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.

 

[Tom Ogilvy]

Useful nontheless.

Sub ShowAll()
On Error GoTo away
ActiveSheet.ShowAllData
Range("C38").Calculate
away:
End Sub

 

[JMay]

That Nailed it!!
Tks again.
Jim

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)

 

[Bob Phillips]

Not saying it isn't, just doesn't help Jim.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Tom Ogilvy]

Sure it does. Maybe not for the problem he complained of. But I also
provided help for that.

 

[Bob Phillips]

Sure it does. Maybe not for the problem he complained of.

Exactly!


But I also provided help for that.

Not originally.