Ideas on how to run a workbook through an "always available" button

[Jat999]

Hi all

I am looking for some ideas on how to allow the user to interact with a workbook with an "always available" button.
The core of the workbook I already have working. It general purpose is to allow users to view weekly production schedules which are generated from an ERP system.
Each worksheet represents a weekly schedule, each tab is named based on the date. Historical sheets are read only, Future sheets, if they already exist can be updated, and if they do not already exist, providing data from the ERP system is available, can be created. Older sheets, say older than 5 weeks will be hidden.
Ideally the users will not need to access the ribbon.

What I think I need is an "Invoking" button which is always available which then invokes a user form to allow user control.

Any thoughts please and maybe an example or two?

Thanks in advance

John

 

[Trevor_S]

Presumably you're trying to avoid putting a button on each worksheet?
You could create another userform containing your invoke button, which you show as Modeless using a Workbook Open event. You could then still work on the workbook while this form was open.

 

[Jat999]

Hi Trevor.

Thanks for responding.

I initially thought about going down the macro button being available on the ribbon, however this would not have been a viable option as it would need setting up for many users and becomes an ongoing maintenance issue. I did also look at someone had written a routine to create and destroy the ribbon button, but far too complex for me.
So I have resorted to a short routine that now copies the button onto the worksheet each time a worksheet is created.
Thanks again for responding

John.

 

[mumps]

You can add a button to the Quick Access Toolbar that you can link to your macro. That button will always be visible regardless of which sheet is the active sheet. You can set the button to work only for that particular workbook. When you save the file, the button will be saved with it so that each time you open the file it is available to click. If this works for you and you need help, please let me know.