Upon investigation, the simplest solution seems to be to:-Make sure you have a
formula that recalculates when filters change. (I used =Count(A:A) in one of
the header cells) - Create a Worksheet_Calculate event macro.
In that macro, you can check to see if the worksheet is filtered: If
(ActiveSheet.FilterMode) Then
Then, you can loop through all filters: for nFilter = 1 to
activesheet.AutoFilter.Filters.Count
and check to see if the filter is selected: If
(ActiveSheet.AutoFilter.Filters(nFilter).On) Then
Now, it's fairly easy to convert the column number to a letter if you're using
less than 27 columns by using chr(nFilter + 64)
But in your example, you used column AB, so something more involved is
required.There's undoubtedly several ways to accomplish this.One way is to do
it mathematically by first using the above function for columns less than
column AA:
If (nFilter < 27) Then
msg2 = msg2 & Chr(nFilter + 64)
ElseThen, to divide by 27 to get the integer number of times
through the alphabet: Char1 = Int(nFilter / 27) If (Char1 >
0) Then msg2 = msg2 & Chr(Char1 + 64)and the remainder is the second letter:
Char2 = nFilter Mod 26
If (Char2 > 0) Then msg2 = msg2 & Chr(Char2 + 64)
Leaving with:
If (nFilter < 27) Then
msg2 = msg2 & Chr(nFilter + 64)
Else
Char1 = Int(nFilter / 27)
If (Char1 > 0) Then msg2 = msg2 & Chr(Char1 + 64)
Char2 = nFilter Mod 26
If (Char2 > 0) Then msg2 = msg2 & Chr(Char2 + 64)
End If
Personally, it is quicker to simply determine the address of the cell in row 1
of each column and split the address into an array and use the first element of
the array:
aCell = Split(Cells(1, nFilter).Address, "$")
msg2 = msg2 & aCell(1)The resulting Event macro looks like:
Private Sub Worksheet_Calculate()
Dim nFilter, msg1, msg2
Dim aCell, Char1 As Integer, Char2 As Integer
If (ActiveSheet.FilterMode) Then
' Sheet is Filtered
msg1 = "Filtered on Column(s): "
msg2 = ""
For nFilter = 1 To ActiveSheet.AutoFilter.Filters.Count
If (ActiveSheet.AutoFilter.Filters(nFilter).On) Then
If (msg2 <> "") Then msg2 = msg2 & ", "
aCell = Split(Cells(1, nFilter).Address, "$")
msg2 = msg2 & aCell(1)
End If
Next nFilter
MsgBox msg1 & msg2
Else
MsgBox "No longer Filtered"
End If
End Sub
hope this helps.
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Monday, August 29, 2016 1:58 PM, Richard <richard.m...@gmail.com> wrote:
I have created a macro that will filter information from a report I generated.
I would like to print on the total line the filter selected, such as
TOTAL FOR EXPENSE GROUP AB
when I select AB from the filter drop down.
How could I do thisThanks in advance,
Rich--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
