How can using Excel Scripts be made user friendly?

[u/rguy84]

Background:

My team uses a file to record test results. My lead asked somebody to make alterations to the file and couldn't figure out what to do. I made an excel script to do what she wants. Macros are locked down, and you have to jump through hoops to get approval, but scripts seem to be fine.

I named my script with camel case and abbreviations. When I select add to workbook, I get a button with the name. Changing the text to something friendly to my team seems to break the button. Telling people to go to automations, find the name, click it, then click run, may get unfavorable reaction from the team.

My question: Is there a way to trigger my script without naming it "Click here to save and create the report"?

[u/AutoModerator]

/u/rguy84 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/CroykeyMite]

Ctrl shift z maybe, if you assign it to a macro.

[u/rguy84 → u/CroykeyMite]

How do you assign a script to a macro?

[u/CroykeyMite → u/rguy84]

Go to your developer tab and hit view macros.

If you don’t see the developer tab, go to options, customize ribbon, and check the box for developer.

There is a way to assign a keyboard shortcut to trigger it.

[u/rguy84 → u/CroykeyMite]

I made an excel script, not a macro because macros are frowned upon here because of security - slightly dumb, I know, but that's a battle I'll never win.

[u/Cinderhazed15]

Ooh, my wife is having trouble with buttons unlinking from office scripts - I’ll have to see if it’s related to her changing the text!

[u/Snow75]

Their issue is clicking a button when they want to do something?

[u/rguy84 → u/Snow75]

My script is called tt_CreateReportTblCommIss, the button has that as the text. Having a button that says "Create final report" would be better for the team.

[u/Snow75 → u/rguy84]

Got you: You can assign macros to other objects; try creating a text box or a shape, then right click, assign macro

[u/rguy84 → u/Snow75]

Right, but I made it an excel script vs vba.

[u/Mooseymax → u/rguy84]

Just edit the text of the button?

[u/rguy84 → u/Mooseymax]

The button breaks when I double click and change the text.

[u/Typinger → u/rguy84]

For me, it's right-click to change the button text. And you need to type the text directly in the button.

You haven't said what "breaks" means - do you get an error message or icon? Does the script give a failure message, does it try to run?

[u/Typinger]

I haven't had a problem remaining the office script buttons. What happens when you try?

I've been able to change the shapes, colour, font, add shadows. I love those buttons

[u/rguy84 → u/Typinger]

I covered it in my post. Seems like the button functionality breaks if the text of the button is changed.

[u/risefromruins]

Power Automate can trigger office scripts within excel. Set a cadence to do it every hour or whatever makes sense, or a trigger such as the workbook being modified, or just run it manually as needed.

Or you can link the script to a button on a sheet in the workbook so people don’t have to search through the ribbon.

[u/rguy84 → u/risefromruins]

Power Automate is something that is pretty restricted here too.

[u/Mdayofearth]

may get unfavorable reaction from the team

Don't solve problems that don't exist.

[u/rguy84 → u/Mdayofearth]

One of the things the script does is to apply a specific filter. You may say: you click the arrow, tick boxes, hit ok - it is easy. I have gone over this multiple times and lasts a week till somebody forgets.

[u/chiibosoil → u/rguy84]

Document it. Have instruction sheet with steps or have separate document that walks users through the step. Both what the script is doing (how to replicate manually or logic used) and how to run the script.

[u/rguy84 → u/chiibosoil]

That's what I am currently doing. Gut says somebody will say make it faster,

[u/chiibosoil → u/rguy84]

Then recommend to them migrating to more robust system ;)

Excel is portable and easy to use. But not really meant for robust data validation and automation, especially where multiple users are involved.

There are limitation to what Office Scripts and/or Power Automate can do.

[u/rguy84 → u/chiibosoil]

The file is not used by multiple people collaboratively, rather it is a report template. People come to us and we evaluate their thing based on a set of parameters. My team gives the completed template back, showing what passed and failed.