ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   CHANGING POSITION OF AUTOFILTER LIST (https://www.excelbanter.com/excel-programming/372358-changing-position-autofilter-list.html)

Doo0592
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

Bernie Deitrick
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

Doo0592
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

Doo0592
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


All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com