|
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
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 |
Do not allow Narrow Down with AutoFilter
On Sep 10, 1:03 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote: 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- Hide quoted text - - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum |
Do not allow Narrow Down with AutoFilter
On Sep 10, 1:39 pm, wrote:
On Sep 10, 1:03 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: 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 ups.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- Hide quoted text - - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum- Hide quoted text - - Show quoted text - Hello, I would like to attach my Excel file to this group so that you may troubleshoot why I can not run the code...how do I do this? I had done this many years ago but can not recall the steps. Sargum |
Do not allow Narrow Down with AutoFilter
On Sep 10, 4:19 pm, wrote:
On Sep 10, 1:39 pm, wrote: On Sep 10, 1:03 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: 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 ups.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- Hide quoted text - - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum- Hide quoted text - - Show quoted text - Hello, I would like to attach my Excel file to this group so that you may troubleshoot why I can not run the code...how do I do this? I had done this many years ago but can not recall the steps. Sargum- Hide quoted text - - Show quoted text - I noticed that Roger posted an http:/ link, how do I? S |
Do not allow Narrow Down with AutoFilter
Hello,
I would like to attach my Excel file to this group so that you may troubleshoot why I can not run the code...how do I do this? I had done this many years ago but can not recall the steps. Sargum- Hide quoted text - - Show quoted text - I noticed that Roger posted an http:/ link, how do I? 1. Don't ever post a file to a non binary newsgroup. 2. Trim your posts if you are going to bottom post 3. Roger offered graciously to receive the file via email from you -- Regards, Peo Sjoblom |
Do not allow Narrow Down with AutoFilter
Hi Sargum
No file arrived. I will check my mailbox again in the morning. You cannot upload files to this NG. Most people will not open any attached files. -- Regards Roger Govier - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum |
Do not allow Narrow Down with AutoFilter
On Sep 10, 5:15 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote: Hi Sargum No file arrived. I will check my mailbox again in the morning. You cannot upload files to this NG. Most people will not open any attached files. -- Regards Roger Govier - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum- Hide quoted text - - Show quoted text - Hello, I appreciate the help. I understand now that I can not post to this site. However, I still do not know how to send my file to anyone. When I click "reply to author" there is no attachment link option. Also, I have been uable to decipher Roger's email address and have sent him mine via the "reply to author" but have have not seen an email from him. Roger, if you are out there...plz reply to my email so then I can send you my file to review. Thx. Sargum |
Do not allow Narrow Down with AutoFilter
On Sep 11, 1:47 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: roger at technology4u dot co dot uk Take out all the spaces, change the at to an @ and change the dot to . This at that dot who dot where becomes The same is true for my email... HTH, Bernie MS Excel MVP wrote in ooglegroups.com... On Sep 10, 5:15 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi Sargum No file arrived. I will check my mailbox again in the morning. You cannot upload files to this NG. Most people will not open any attached files. -- Regards Roger Govier - Show quoted text - I have sent you an abridged version of the file. thanks. Sargum- Hide quoted text - - Show quoted text - Hello, I appreciate the help. I understand now that I can not post to this site. However, I still do not know how to send my file to anyone. When I click "reply to author" there is no attachment link option. Also, I have been uable to decipher Roger's email address and have sent him mine via the "reply to author" but have have not seen an email from him. Roger, if you are out there...plz reply to my email so then I can send you my file to review. Thx. Sargum- Hide quoted text - - Show quoted text - Oh! Thanks. Sargum |
Do not allow Narrow Down with AutoFilter
Hi Sargum
File received, corrected and returned. The main problem was that you had failed to include a cell on the sheet with the volatile formula = TODAY() as per Debra's original instruction. It is the volatility of that formula which triggers the Calculate event code on the sheet. I added the formula to cell V1 of your sheet. The content can be hidden if required by setting the format of cell V1 to be White font on a white background. I added a few more rows to your data, with "X's" in dfieefernt locations in the column to allow a selection of X on the dropdown, which did not include all rows. As soon as you try to make another selection, the first selection is cleared. -- Regards Roger Govier |
Do not allow Narrow Down with AutoFilter
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 |
| All times are GMT +1. The time now is 02:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com