Jim
Thanks once again.
I was hoping to use buttons and place the autofilter on top of them as
they would be preferable since they would size with the column as the
need arose.
Is this possible I wonder?

Thanks in advance

ML

On Jun 13, 7:50*am, Mr X Z wrote:
Jim
Thanks once again.
I was hoping to use buttons and place the autofilter on top of them as
they would be preferable since they would size with the column as the
need arose.
Is this possible I wonder?

Thanks in advance

ML

Bump...

Rebound...
Is what possible and what happens when you try it?



"Mr X Z"
wrote in message
...
On Jun 13, 7:50 am, Mr X Z wrote:
Jim
Thanks once again.
I was hoping to use buttons and place the autofilter on top of them as
they would be preferable since they would size with the column as the
need arose.
Is this possible I wonder?

Thanks in advance

ML

Bump...

Hi Jim
Thank you for replying. Apologies for not making myself clear.
The buttons are great but they are fixed in size - I was hoping to
fill the cell that the heading was in plus if that column was resized
the button too would resize. Here is my pathetic attempt...

Sub SetupOneTime()
'adds rectangle at top of each column
'code written by Dave Peterson 2005-10-22
'screwed around with by Jim Cone 2011-06-12
' so as to use buttons instead of rectangles.

Dim myRng As Range
Dim myCell As Range
Dim curWks As Worksheet
'Dim myRect As Shape
Dim btn As Button
Dim iCol As Integer
Dim iFilter As Integer
iCol = 7 'number of columns
iFilter = 14 'width of drop down arrow


Set curWks = ActiveSheet
With curWks
Set myRng = .Range("a1").Resize(1, iCol)
For Each myCell In myRng.Cells
With myCell

Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
Selection.AutoFilter
'Set myRect = .Parent.Shapes.AddFormControl(xlButtonControl, _
Top:=.Top, Height:=.Height, _
Width:=.Width - iFilter, Left:=.Left)

End With
myRect.OnAction = ThisWorkbook.Name & "!SortTable"
myRect.TextFrame.Characters.Text = myCell.Value2
Next myCell
End With
End Sub

The width of the buttons is reduced to allow the filter arrows to fit in each cell without
overlapping the button.
The buttons won't automatically resize unless the button is exactly the same size as the cell.
So to get the buttons to resize, simply remove the " - iFilter" portion of the code to get:
"Width:=.Width"

Also, each button must have its "move and size with cells" property checkmarked (set to true).
On my system, that is the default - it doesn't have to be set.

I know of no way to change the size/position of the autofilter arrows.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Lottery Numbers.xls: two national lotteries & twelve state lotteries - in the free folder)




"Mr X Z"
wrote in message
...
Hi Jim
Thank you for replying. Apologies for not making myself clear.
The buttons are great but they are fixed in size - I was hoping to
fill the cell that the heading was in plus if that column was resized
the button too would resize. Here is my pathetic attempt...
-snip-

Muchos gratias Jim - you've nailed it!

ML