08-19-2020 10:52 AM
08-19-2020 10:52 AM
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
08-19-2020 11:55 AMSolution
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:
08-19-2020 12:07 PM
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.
08-19-2020 12:47 PM
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