Attaching macro to a drawing shape

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Apr 8, 2002
Messages
912
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This seems to work with Advanced Filter:

Code:
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

Well-known Member
Joined
Apr 8, 2002
Messages
912

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
ActiveSheet.AutoFilterMode = False

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

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
Thanks Andrew I have put:
ActiveSheet.AutoFilterMode = False

before and after Else.
Many thanks for your help.
 

Forum statistics

Threads
1,147,822
Messages
5,743,400
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top