Function not completely volatile help

  • Thread starter Thread starter JMay
  • Start date Start date
J

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
 
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)
 
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,
 
Oopps,,, Bob --this destroys (deletes) my formula

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

Any other thought?

Jim
 
shaking head

--
Regards,
Tom Ogilvy

JMay said:
Oopps,,, Bob --this destroys (deletes) my formula

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

Any other thought?

Jim
Click to expand...
 
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)
 
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)
 
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.
 
Useful nontheless.

Sub ShowAll()
On Error GoTo away
ActiveSheet.ShowAllData
Range("C38").Calculate
away:
End Sub
 
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)
Click to expand...
 
Not saying it isn't, just doesn't help Jim.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Sure it does. Maybe not for the problem he complained of. But I also
provided help for that.
 
Back
Top