Excel 2016 creating button functions without using VBA / Macros

[Stuart_Bean]

Hello 

I am creating an excel document in 2016 and need to use buuttons to trigger some functions.

THis is goingto be housed in Sharepoint and becasue of this and ourr ICT restrictionson macros in workbooks Macros and VBA as .XLSM files do not work.

My only option is to use functions and formulas

THere are existing Buttons on the toolbar that you can use to perform the function i need I am trying to replicate that function as a button press on screen.

The Main Function I am trying to do is a simple Clear all filters button. effectively when you press the button it emulates the pressing of "ALT+A+C"

there is no option I see to assign this keyboard function to a shape in excel

Please can someone help

[#2 · Accepted solution]

@Stuart_Bean 

A button on a worksheet can only execute VBA code, so that's out.

You can add the Clear All Filters button to the Quick Access Toolbar for that specific workbook, so it will be available to everyone who opens the workbook:

• Click the dropdown arrow on the right hand side of the Quick Access Toolbar.
• Select 'More Commands...' from the dropdown menu.
• Select the active workbook instead of 'For all documents (default)' from the Customize Quick Access Toolbar dropdown in the upper right corner.
• Select 'Data Tab' (or 'All Commands') from the 'Choose commands from' dropdown.
• Select 'Clear All Filters'.
• Click 'Add >>'
• If desired, use the up and down arrow buttons on the right to move the 'Clear All Filters' button to the desired position.
• Click OK.

 

[#3]

@Stuart_Bean 

Hi Stuart,

In the Active Controls the toggle button can be linked to a cell to give you a true/false entry in the cell. Aside from a formula giving an answer when true and a blank when false, there is no way to make a formula perform "tasks" of a functional nature without macros. Hans is correct.

[#4]

@Hans Vogelaar 

Thankyou very much for confirming what I was starting to accept.

I have done the customised bar and it seems to work acceptably however there is no way of making the button clear to the inexperienced user (this is why I wanted to use Big coloured buttons.)

I was hoping to have this spreadsheet work direct of the sharepoint so the users didnt have to download it (as there are monthly updates that they may miss)

As there are other functions I want on this workbook I know won't work without VBA

I am looking at allowing the user to download and have a macro run that check the version (date) and update and save the the file if necessary when they open the document (this could be a big task)

but it will get aroud the fact that sharepoint doesn't like Macro files 

THankyou for your help

[#5]

Hi Did you solve this problem?

[#6]

Another method of getting around the restriction on macros\VBA in the workbook would be to create an Excel Add-in and have the macros check for a particular feature that you insert into the workbook and only run if that feature is present.