Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
Please don't multi-post: cross-post, if you must, but only post once.
See my reply in .misc HTH, Bernie MS Excel MVP "Doo0592" 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.programming
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
I removed the post from misc because I realised it was in the wrong
forum. It obviously hasn't worked. Bernie Deitrick wrote: Please don't multi-post: cross-post, if you must, but only post once. See my reply in .misc HTH, Bernie MS Excel MVP "Doo0592" 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.programming
|
|||
|
|||
CHANGING POSITION OF AUTOFILTER LIST
I removed the post from misc because I realised it was in the wrong
forum. It obviously hasn't worked. Bernie Deitrick wrote: Please don't multi-post: cross-post, if you must, but only post once. See my reply in .misc HTH, Bernie MS Excel MVP "Doo0592" 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 position of last used cell | Excel Discussion (Misc queries) | |||
images in excel keep changing position | Excel Discussion (Misc queries) | |||
Changing Right Position of Chart | Charts and Charting in Excel | |||
CHANGING POSITION OF AUTOFILTER LIST | Excel Discussion (Misc queries) | |||
changing the font size on the drop down of a list (autofilter). | Excel Programming |