ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   CHANGING POSITION OF AUTOFILTER LIST (https://www.excelbanter.com/excel-discussion-misc-queries/108855-changing-position-autofilter-list.html)

[email protected]
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
 
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

[email protected]
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

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

[email protected]
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


All times are GMT +1. The time now is 02:27 PM.

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