Attaching macro to a drawing shape

[inarbeth]

I wish to attach macros to buttons (i.e. shapes) on the worksheet. This is to make it easy for users.
I have two questions, one general adn one specific:
1. Is there an easy way to attach to a shape Excel's native macros, eg autofilter, or do I have to record a new macro.
2. I want to attach a Show All macro to a button but I need an error handler so that if there is no filter in place the macro does not try to run (which will lead to it crashing) but simply shows a message box to say: "Please filter before clicking this button."

Many thanks

 

[Andrew Poulsom]

1. You will need to write a procedure.

2. The FilterMode Property of the Worksheet object returns True if the worksheet contains a filtered list in which there are hidden rows. The AutoFilterMode Property of the of the Worksheet object returns True if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property.

 

[inarbeth]

On 2002-11-05 11:05, Andrew Poulsom wrote:
1. You will need to write a procedure.

2. The FilterMode Property of the Worksheet object returns True if the worksheet contains a filtered list in which there are hidden rows. The AutoFilterMode Property of the of the Worksheet object returns True if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property.

Andrew
What is the code for that please? In fact I want the button to reset whether AutoFilter or Advanced Filter has been used.
I have got as far as this, which does not work:

Sub ShowAllLeases()
If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.ShowAllData
Range("S3").Select

Else
Exit Sub
End If
End Sub

 

[Andrew Poulsom]

This seems to work with Advanced Filter:

Sub Test()
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    Else
'   *** Change the top left cell to suit ***
        ActiveSheet.Range("A5").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace
    End If
End Sub

 

[inarbeth]

Thanks Andrew, sorry for the delay in replying - we had a problem with our internet connection.
I have modified your code to:
Sub ShowAllLeases()
'
' ShowAllLeases Macro
' Macro recorded 11/5/2002 by inarbeth
'

'
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
Else
ActiveSheet.AutoFilterMode = False

Range("S3").Select

End If
End Sub

which simply checks if there is an advanced or auto filter in place. If there is it shows all data. If not it selects S3. As a final flourish to the code, if an autofilter has been selected but not used the macro does not turn off the dropdown arrows. If the filter is in place the macro turns it off and loses the dropdown arrows. What is the code to turn off AutoFilter's dropdown arrows in both cases? Many thanks.

 

[Andrew Poulsom]

ActiveSheet.AutoFilterMode = False

removes the arrows. So maybe you need it in both conditions in your If...Then...Else construct.

 

[inarbeth]

Thanks Andrew I have put:
ActiveSheet.AutoFilterMode = False

before and after Else.
Many thanks for your help.