Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum

Sargum,

Copy the code below, then right-click the sheet tab and select "View Code". Paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Public myCol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If myCol = 0 Then myCol = Target.Cells(1).Column
If myCol < Target.Cells(1).Column Then
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
myCol = Target.Cells(1).Column
End If

End Sub

wrote in message ps.com...
Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum

Hi Bernie

That works perfectly as long as the user selects the cell with the dropdown,
before selecting the dropdown itself.
Regrettably, if the user goes straight to the filter dropdown button, whilst
a selection is in place, the SelectionChange event doesn't get triggered.
I have been trying to figure out a way around this, and decided that a
modification of Debra's code (that I directed the OP to), would achieve both
the task of colouring the active filtered cell, and preventing 2 filters
being set at the same time.

Selecting a second filter, causes ShowAlldata, and the user has to select
the filter required again.

Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
'modified by Roger Govier

Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer

If ActiveSheet.AutoFilterMode Then
' additional lines of code inserted here

Set af = ActiveSheet.AutoFilter
iFilterCount = 0
For Each fFilter In af.Filters
If fFilter.On Then iFilterCount = iFilterCount + 1
Next

If iFilterCount 1 Then
Rows(1).EntireRow.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData
Exit Sub
End If

'additional lines end here

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



--
Regards
Roger Govier



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sargum,

Copy the code below, then right-click the sheet tab and select "View
Code". Paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP


Public myCol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If myCol = 0 Then myCol = Target.Cells(1).Column
If myCol < Target.Cells(1).Column Then
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
myCol = Target.Cells(1).Column
End If

End Sub

wrote in message
ps.com...
Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum

Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip


--
Regards
Roger Govier



wrote in message
ps.com...
Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum

On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

ps.com...



Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hello,

I appreciate your help but I did as you mentioned...I added the code
but it did not automatically go back to (All) after one moved to the
next column to filter on a different subject. It is narrowing down
still as it did before.

Sargum

On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

ps.com...



Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum

Obviously, the code worked for me.

Do you have events disabled?

If so, use this macro:

Sub ReEnable()
Application.EnableEvents = True
End Sub


Did you put the code into a regular code module or into the window that appeared when you r-clicked
and chose "View Code"?

HTH,
Bernie
MS Excel MVP


wrote in message ps.com...
On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

ps.com...



Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum

On Sep 10, 11:39 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Obviously, the code worked for me.

Do you have events disabled?

If so, use this macro:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Did you put the code into a regular code module or into the window that appeared when you r-clicked
and chose "View Code"?

HTH,
Bernie
MS Excel MVP



wrote in glegroups.com...
On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

oups.com...

Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum- Hide quoted text -

- Show quoted text -

Hi all,

Still have had no luck getting the code to work. Have enabled macros,
changed security level to medium, did right click and choose "view
code" and paste into window. This is the code I have put in:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
'modified by Roger Govier


Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer


If ActiveSheet.AutoFilterMode Then
' additional lines of code inserted here


Set af = ActiveSheet.AutoFilter
iFilterCount = 0
For Each fFilter In af.Filters
If fFilter.On Then iFilterCount = iFilterCount + 1
Next


If iFilterCount 1 Then
Rows(1).EntireRow.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData
Exit Sub
End If


'additional lines end here


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

Hi

The code (both Debra's original, and my modified) works fine for me.

As Bernie says, are you sure that you have pasted into the Sheet, and not in
a regular module.
In the immediate window of the VBE (press Control+G if it is not visible)
type
Application.EnableEvents = True and then press Enter

Click back to your sheet, and see if it works for you then.

If you are still having a problem, send me an email, and I will post the
file to you.
Send to
roger at technology4u dot co dot uk
Do the obvious thing with dot and at.
--
Regards
Roger Govier



wrote in message
ps.com...
On Sep 10, 11:39 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Obviously, the code worked for me.

Do you have events disabled?

If so, use this macro:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Did you put the code into a regular code module or into the window that
appeared when you r-clicked
and chose "View Code"?

HTH,
Bernie
MS Excel MVP



wrote in
glegroups.com...
On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may
help
users to recognise that a filter has already been set. Although the
small
arrow on the filter button turns blue when selection is on, this is
hard to
see.

Debra Dalgleish has several downloadable example sheets on her site,
where
the cell containing the heading becomes coloured if a filter is
applied.
Users could be told that not more than one heading should be coloured
for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

oups.com...

Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users
do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do
was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow
down...Hence,
how do I make it so that if one moves to another column to filter,
it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be
of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum- Hide quoted text -

- Show quoted text -

Hi all,

Still have had no luck getting the code to work. Have enabled macros,
changed security level to medium, did right click and choose "view
code" and paste into window. This is the code I have put in:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
'modified by Roger Govier


Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer


If ActiveSheet.AutoFilterMode Then
' additional lines of code inserted here


Set af = ActiveSheet.AutoFilter
iFilterCount = 0
For Each fFilter In af.Filters
If fFilter.On Then iFilterCount = iFilterCount + 1
Next


If iFilterCount 1 Then
Rows(1).EntireRow.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData
Exit Sub
End If


'additional lines end here


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

On Sep 10, 11:39 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Obviously, the code worked for me.

Do you have events disabled?

If so, use this macro:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Did you put the code into a regular code module or into the window that appeared when you r-clicked
and chose "View Code"?

HTH,
Bernie
MS Excel MVP



wrote in glegroups.com...
On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

oups.com...

Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum- Hide quoted text -

- Show quoted text -

Hi,

I have macros enabled, my security set to medium, and pasted in "View
Code" window and still nothing seems to have been implemented. Does
anyone have any ideas? I had put in the following code:

Sub ReEnable()
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
'modified by Roger Govier


Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer


If ActiveSheet.AutoFilterMode Then
' additional lines of code inserted here


Set af = ActiveSheet.AutoFilter
iFilterCount = 0
For Each fFilter In af.Filters
If fFilter.On Then iFilterCount = iFilterCount + 1
Next


If iFilterCount 1 Then
Rows(1).EntireRow.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData
Exit Sub
End If


'additional lines end here


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

Sargum

On Sep 10, 12:53 pm, wrote:
On Sep 10, 11:39 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:





Obviously, the code worked for me.

Do you have events disabled?

If so, use this macro:

Sub ReEnable()
Application.EnableEvents = True
End Sub

Did you put the code into a regular code module or into the window that appeared when you r-clicked
and chose "View Code"?

HTH,
Bernie
MS Excel MVP

wrote in glegroups.com...
On Sep 10, 10:58 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Whilst the following suggestion won't change the selection, it may help
users to recognise that a filter has already been set. Although the small
arrow on the filter button turns blue when selection is on, this is hard to
see.

Debra Dalgleish has several downloadable example sheets on her site, where
the cell containing the heading becomes coloured if a filter is applied.
Users could be told that not more than one heading should be coloured for
their selection to be correct.

http://www.contextures.com/FilterColour.zip

--
Regards
Roger Govier

wrote in message

oups.com...

Hello,

I have an Excel 2003 file with 8 columns that one could filter on.
The AutoFilter function is set to ON. I have found that the users do
not always remember to click on (All) in the filter drop down menu
before moving to the next column and filtering...they end up with
narrowing down on their filter choices when all they wanted to do was
filter exclusively on the next column criteria.

With this dataset, there will never be a need to narrow down...Hence,
how do I make it so that if one moves to another column to filter, it
automatically sets everything back to (All)? There are way too many
users to train to select (All) every time they move to a different
column to filter on a different subject so a solution here would be of
great help!

Sargum- Hide quoted text -

- Show quoted text -

Hi Roger,

I like this suggestion a lot and have added the code...I right-clicked
and chose "View Code" and pasted. However, similar to Bernie's code,
nothing happened. Is there something else I am supposed to do after I
paste the code and close VB? I must be missing something here since
both yours and Bernie's code have not worked for me yet. I will play
around with it.

Sargum- Hide quoted text -

- Show quoted text -

Hi,

I have macros enabled, my security set to medium, and pasted in "View
Code" window and still nothing seems to have been implemented. Does
anyone have any ideas? I had put in the following code:

Sub ReEnable()
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
'modified by Roger Govier

Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer

If ActiveSheet.AutoFilterMode Then
' additional lines of code inserted here

Set af = ActiveSheet.AutoFilter
iFilterCount = 0
For Each fFilter In af.Filters
If fFilter.On Then iFilterCount = iFilterCount + 1
Next

If iFilterCount 1 Then
Rows(1).EntireRow.Interior.ColorIndex = xlNone
ActiveSheet.ShowAllData
Exit Sub
End If

'additional lines end here

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

Sargum- Hide quoted text -

- Show quoted text -

Hello All,

Roger informed me that:

"The underlying problem however, was that you didn't include a cell
with the volatile formula =TODAY() as in Debra's original code.

I added that to cell V1.

It is essential, as the volatility of the function is what triggers
the Calculate event code for the sheet. Without it, nothing happens.

If you don't want the value to show up, set the format of V1 to be
white text, and it will not show against a white background."

So, now the code works. Thanks so much for all of your efforts!

Sargum