Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
I use DataFilterAutoFilter frequently for multiple columns. When a filter
has been used on any column the "triangle" marker on the tab changes from black to blue. Because I frequently work with my sheets at 75% I have a problem distinguishing one color from another so can't always find the columns I've filtered. It would be very helpful if the entire tab changed color, or if that 's not possible, change the "triangle" to a bright color such as red, pink, or orange. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
Paste the below code into the Sheet module of the appropriate sheet; then
in a blank cell enter =Now() - to call the Calculate event which will run the code Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer If ActiveSheet.AutoFilterMode Then Set af = ActiveSheet.AutoFilter iFilterCount = 1 For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 6 Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = xlNone End If iFilterCount = iFilterCount + 1 Next fFilter Else Rows(1).EntireRow.Interior.ColorIndex = xlNone End If End Sub "FeFi" wrote: I use DataFilterAutoFilter frequently for multiple columns. When a filter has been used on any column the "triangle" marker on the tab changes from black to blue. Because I frequently work with my sheets at 75% I have a problem distinguishing one color from another so can't always find the columns I've filtered. It would be very helpful if the entire tab changed color, or if that 's not possible, change the "triangle" to a bright color such as red, pink, or orange. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
Unfortunately my knowledge of the typeof code you furnished is basically
non-existent. What does this do? Is this a permanent fix for the color on the AutoFilter tabs on all sheets? ? ? ? ? ? ? "Jim May" wrote: Paste the below code into the Sheet module of the appropriate sheet; then in a blank cell enter =Now() - to call the Calculate event which will run the code Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer If ActiveSheet.AutoFilterMode Then Set af = ActiveSheet.AutoFilter iFilterCount = 1 For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 6 Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = xlNone End If iFilterCount = iFilterCount + 1 Next fFilter Else Rows(1).EntireRow.Interior.ColorIndex = xlNone End If End Sub "FeFi" wrote: I use DataFilterAutoFilter frequently for multiple columns. When a filter has been used on any column the "triangle" marker on the tab changes from black to blue. Because I frequently work with my sheets at 75% I have a problem distinguishing one color from another so can't always find the columns I've filtered. It would be very helpful if the entire tab changed color, or if that 's not possible, change the "triangle" to a bright color such as red, pink, or orange. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
Jim may be otherwise engaged.
What he has posted is worksheet event code which is triggered by the =NOW() formula when you click on an Autofilter dropdown arrow. The cell with that arrow will be highlighted in yellow. Copy the code then right-click on the sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Click on one of the AF arrows and select something to filter on. Gord Dibben MS Excel MVP On Fri, 7 Mar 2008 19:22:00 -0800, FeFi wrote: Unfortunately my knowledge of the typeof code you furnished is basically non-existent. What does this do? Is this a permanent fix for the color on the AutoFilter tabs on all sheets? ? ? ? ? ? ? "Jim May" wrote: Paste the below code into the Sheet module of the appropriate sheet; then in a blank cell enter =Now() - to call the Calculate event which will run the code Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer If ActiveSheet.AutoFilterMode Then Set af = ActiveSheet.AutoFilter iFilterCount = 1 For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 6 Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = xlNone End If iFilterCount = iFilterCount + 1 Next fFilter Else Rows(1).EntireRow.Interior.ColorIndex = xlNone End If End Sub "FeFi" wrote: I use DataFilterAutoFilter frequently for multiple columns. When a filter has been used on any column the "triangle" marker on the tab changes from black to blue. Because I frequently work with my sheets at 75% I have a problem distinguishing one color from another so can't always find the columns I've filtered. It would be very helpful if the entire tab changed color, or if that 's not possible, change the "triangle" to a bright color such as red, pink, or orange. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
Thanks for the explanation. I copied and pasted the code as instructed but
when I enter the =NOW() formula I get: Visual Basic "Compile error: Syntax error" with the " Set af = ActiveSheet.AutoFilter" line of code highlighted. I assume it's something minor but I don't know how to fix it. "Gord Dibben" wrote: Jim may be otherwise engaged. What he has posted is worksheet event code which is triggered by the =NOW() formula when you click on an Autofilter dropdown arrow. The cell with that arrow will be highlighted in yellow. Copy the code then right-click on the sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Click on one of the AF arrows and select something to filter on. Gord Dibben MS Excel MVP On Fri, 7 Mar 2008 19:22:00 -0800, FeFi wrote: Unfortunately my knowledge of the typeof code you furnished is basically non-existent. What does this do? Is this a permanent fix for the color on the AutoFilter tabs on all sheets? ? ? ? ? ? ? "Jim May" wrote: Paste the below code into the Sheet module of the appropriate sheet; then in a blank cell enter =Now() - to call the Calculate event which will run the code Private Sub Worksheet_Calculate() Dim af As AutoFilter Dim fFilter As Filter Dim iFilterCount As Integer If ActiveSheet.AutoFilterMode Then Set af = ActiveSheet.AutoFilter iFilterCount = 1 For Each fFilter In af.Filters If fFilter.On Then af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = 6 Else af.Range.Cells(1, iFilterCount) _ .Interior.ColorIndex = xlNone End If iFilterCount = iFilterCount + 1 Next fFilter Else Rows(1).EntireRow.Interior.ColorIndex = xlNone End If End Sub "FeFi" wrote: I use DataFilterAutoFilter frequently for multiple columns. When a filter has been used on any column the "triangle" marker on the tab changes from black to blue. Because I frequently work with my sheets at 75% I have a problem distinguishing one color from another so can't always find the columns I've filtered. It would be very helpful if the entire tab changed color, or if that 's not possible, change the "triangle" to a bright color such as red, pink, or orange. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
You pasted Jim's code into the worksheet module as written?
From here I can't tell what the pasted code looks like so hard to troublesheet. Syntax errors usually mean a spelling mistake or one long line wrapping to 2 lines. When you pasted in did you see any lines of red text which would indicate a poor copy/paste? I copied directly from Jim's post and the code worked "out of the box" for me. If you can't see anything obvious, email the workbook to me. Change the AT and DOT for my email address. Gord On Sat, 8 Mar 2008 11:49:01 -0800, FeFi wrote: Thanks for the explanation. I copied and pasted the code as instructed but when I enter the =NOW() formula I get: Visual Basic "Compile error: Syntax error" with the " Set af = ActiveSheet.AutoFilter" line of code highlighted. I assume it's something minor but I don't know how to fix it. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
I couldn't see anything obvious so decided to delete everything and try the
copy and paste again in my trial sheet. I got the same result, so I deleted the entire file. I then opened another trial file, did the copy & paste as instructed, and the code worked "out of the box" for me as well. Even though this works well for existing data, and can certainly be applied on an "as needed" basis, I'd still like to see the default "blue" triangle changed to another color in the future - my eyes just keep getting older! Thanks so much for your patience and continued suggestions. "Gord Dibben" wrote: You pasted Jim's code into the worksheet module as written? From here I can't tell what the pasted code looks like so hard to troublesheet. Syntax errors usually mean a spelling mistake or one long line wrapping to 2 lines. When you pasted in did you see any lines of red text which would indicate a poor copy/paste? I copied directly from Jim's post and the code worked "out of the box" for me. If you can't see anything obvious, email the workbook to me. Change the AT and DOT for my email address. Gord On Sat, 8 Mar 2008 11:49:01 -0800, FeFi wrote: Thanks for the explanation. I copied and pasted the code as instructed but when I enter the =NOW() formula I get: Visual Basic "Compile error: Syntax error" with the " Set af = ActiveSheet.AutoFilter" line of code highlighted. I assume it's something minor but I don't know how to fix it. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
AutoFilter Tabs
Maybe in version 14 of Excel.
Gord On Sat, 8 Mar 2008 13:47:01 -0800, FeFi wrote: I couldn't see anything obvious so decided to delete everything and try the copy and paste again in my trial sheet. I got the same result, so I deleted the entire file. I then opened another trial file, did the copy & paste as instructed, and the code worked "out of the box" for me as well. Even though this works well for existing data, and can certainly be applied on an "as needed" basis, I'd still like to see the default "blue" triangle changed to another color in the future - my eyes just keep getting older! Thanks so much for your patience and continued suggestions. "Gord Dibben" wrote: You pasted Jim's code into the worksheet module as written? From here I can't tell what the pasted code looks like so hard to troublesheet. Syntax errors usually mean a spelling mistake or one long line wrapping to 2 lines. When you pasted in did you see any lines of red text which would indicate a poor copy/paste? I copied directly from Jim's post and the code worked "out of the box" for me. If you can't see anything obvious, email the workbook to me. Change the AT and DOT for my email address. Gord On Sat, 8 Mar 2008 11:49:01 -0800, FeFi wrote: Thanks for the explanation. I copied and pasted the code as instructed but when I enter the =NOW() formula I get: Visual Basic "Compile error: Syntax error" with the " Set af = ActiveSheet.AutoFilter" line of code highlighted. I assume it's something minor but I don't know how to fix it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
Can i set up tabs within tabs on Excel? | Excel Worksheet Functions | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) | |||
Autofilter bug? | Excel Discussion (Misc queries) |