Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
Hi all,
Looking for VB code to change the opening position of the autofilter list. I added this code to compensate for the older versions of excel not allowing access to the autofilter if the worksheet is protected but now all the autofilter lists (if over a certain lenght) appear at the top of the header column instead of at the bottom. Unfortunately this is cutting off access to some of the data in the list. Also, you cannot view the full text in the formula toolbar if it is over the lenght of the cell anymore. Can anyone solve this for me? Cheers Doo x 'Initiate reminder message to appear on first of everymont for achiving and check for filter, turn on if none exists Private Sub Workbook_Open() If Month(Date) < Sheet2.Range("A1").Value And _ Day(Date) = 1 Then MsgBox ("Please archive this tracker today. Thanks.") Sheet2.Range("A1").Value = Month(Date) End If With Worksheets("sheet1") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet2") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .AutoFilter.Position = bottom .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet3") If Not .AutoFilterMode Then .Range("B2:H2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 4") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet5") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 6") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 7") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
AFAIK, the autofilter property of the worksheet object does not have a position property, at least
through XL2003. So this line: .AutoFilter.Position = bottom should not have compiled.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, Looking for VB code to change the opening position of the autofilter list. I added this code to compensate for the older versions of excel not allowing access to the autofilter if the worksheet is protected but now all the autofilter lists (if over a certain lenght) appear at the top of the header column instead of at the bottom. Unfortunately this is cutting off access to some of the data in the list. Also, you cannot view the full text in the formula toolbar if it is over the lenght of the cell anymore. Can anyone solve this for me? Cheers Doo x 'Initiate reminder message to appear on first of everymont for achiving and check for filter, turn on if none exists Private Sub Workbook_Open() If Month(Date) < Sheet2.Range("A1").Value And _ Day(Date) = 1 Then MsgBox ("Please archive this tracker today. Thanks.") Sheet2.Range("A1").Value = Month(Date) End If With Worksheets("sheet1") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet2") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .AutoFilter.Position = bottom .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet3") If Not .AutoFilterMode Then .Range("B2:H2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 4") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet5") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 6") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 7") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
No it didn't. That's why I posted the code to the forum to see if there
was a property. I now have the problem that I cannot unflter the column without having to minimise the screen first to allow access the the top of the filter list. My end users will not accept this. I understand that all officer applications tend to move objects of this nature to different places depending on how far down you have scrolled on the screen etc. Unfortunately, it won't change back. Bernie Deitrick wrote: AFAIK, the autofilter property of the worksheet object does not have a position property, at least through XL2003. So this line: .AutoFilter.Position = bottom should not have compiled.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, Looking for VB code to change the opening position of the autofilter list. I added this code to compensate for the older versions of excel not allowing access to the autofilter if the worksheet is protected but now all the autofilter lists (if over a certain lenght) appear at the top of the header column instead of at the bottom. Unfortunately this is cutting off access to some of the data in the list. Also, you cannot view the full text in the formula toolbar if it is over the lenght of the cell anymore. Can anyone solve this for me? Cheers Doo x 'Initiate reminder message to appear on first of everymont for achiving and check for filter, turn on if none exists Private Sub Workbook_Open() If Month(Date) < Sheet2.Range("A1").Value And _ Day(Date) = 1 Then MsgBox ("Please archive this tracker today. Thanks.") Sheet2.Range("A1").Value = Month(Date) End If With Worksheets("sheet1") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet2") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .AutoFilter.Position = bottom .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet3") If Not .AutoFilterMode Then .Range("B2:H2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 4") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet5") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 6") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 7") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
When you post, you should really state what your problem is. Have you tried using Window / Freeze
pane to show the header row of the drop downs from the autofilter? That would allow the user access to the top of the filter list. HTH, Bernie MS Excel MVP wrote in message ups.com... No it didn't. That's why I posted the code to the forum to see if there was a property. I now have the problem that I cannot unflter the column without having to minimise the screen first to allow access the the top of the filter list. My end users will not accept this. I understand that all officer applications tend to move objects of this nature to different places depending on how far down you have scrolled on the screen etc. Unfortunately, it won't change back. Bernie Deitrick wrote: AFAIK, the autofilter property of the worksheet object does not have a position property, at least through XL2003. So this line: .AutoFilter.Position = bottom should not have compiled.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, Looking for VB code to change the opening position of the autofilter list. I added this code to compensate for the older versions of excel not allowing access to the autofilter if the worksheet is protected but now all the autofilter lists (if over a certain lenght) appear at the top of the header column instead of at the bottom. Unfortunately this is cutting off access to some of the data in the list. Also, you cannot view the full text in the formula toolbar if it is over the lenght of the cell anymore. Can anyone solve this for me? Cheers Doo x 'Initiate reminder message to appear on first of everymont for achiving and check for filter, turn on if none exists Private Sub Workbook_Open() If Month(Date) < Sheet2.Range("A1").Value And _ Day(Date) = 1 Then MsgBox ("Please archive this tracker today. Thanks.") Sheet2.Range("A1").Value = Month(Date) End If With Worksheets("sheet1") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet2") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .AutoFilter.Position = bottom .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet3") If Not .AutoFilterMode Then .Range("B2:H2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 4") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet5") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 6") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 7") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
No this wouldn't solve the problem. The header row is already frozen. I
have solved the problem by altering the height of the header row. As I said earlier. Office applications tend to move these kinds of objects into "better" positions depending on how many items are in the list (in this case). Problem was posted before (as you mentioned) in duplicate forums. I posted in here by accident and then removed it immediately. You obvoiously posted back just before it was removed. And the problem is always in the relpy. I won't be posting to this forum again. Bernie Deitrick wrote: When you post, you should really state what your problem is. Have you tried using Window / Freeze pane to show the header row of the drop downs from the autofilter? That would allow the user access to the top of the filter list. HTH, Bernie MS Excel MVP wrote in message ups.com... No it didn't. That's why I posted the code to the forum to see if there was a property. I now have the problem that I cannot unflter the column without having to minimise the screen first to allow access the the top of the filter list. My end users will not accept this. I understand that all officer applications tend to move objects of this nature to different places depending on how far down you have scrolled on the screen etc. Unfortunately, it won't change back. Bernie Deitrick wrote: AFAIK, the autofilter property of the worksheet object does not have a position property, at least through XL2003. So this line: .AutoFilter.Position = bottom should not have compiled.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, Looking for VB code to change the opening position of the autofilter list. I added this code to compensate for the older versions of excel not allowing access to the autofilter if the worksheet is protected but now all the autofilter lists (if over a certain lenght) appear at the top of the header column instead of at the bottom. Unfortunately this is cutting off access to some of the data in the list. Also, you cannot view the full text in the formula toolbar if it is over the lenght of the cell anymore. Can anyone solve this for me? Cheers Doo x 'Initiate reminder message to appear on first of everymont for achiving and check for filter, turn on if none exists Private Sub Workbook_Open() If Month(Date) < Sheet2.Range("A1").Value And _ Day(Date) = 1 Then MsgBox ("Please archive this tracker today. Thanks.") Sheet2.Range("A1").Value = Month(Date) End If With Worksheets("sheet1") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet2") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .AutoFilter.Position = bottom .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet3") If Not .AutoFilterMode Then .Range("B2:H2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 4") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet5") If Not .AutoFilterMode Then .Range("B2:X2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 6") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With With Worksheets("sheet 7") If Not .AutoFilterMode Then .Range("B2:E2").AutoFilter End If .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Where Data Validation List Boxes Open in Mac Excel 2004 | Excel Discussion (Misc queries) | |||
deleting duplicated cells in a list via advanced autofilter? | Excel Discussion (Misc queries) | |||
"Unpivot Technique" - Changing a Table Into A List | Excel Discussion (Misc queries) | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
How to count number of occurences in an autofilter list | Excel Worksheet Functions |