Have a macro enabled excel file that tracks non conformances in a database. Created a user form to add to database but also gets printed to be worked on the manufacturing floor. Want to share this file so that anyone can create a non-conformance report for the database and the manufacturing floor. It will create a new NCR # for each form that's created and will populate the database for metrics reporting. There is a sheet with two buttons, one for most users to create the non-conformance report and a admin button to maintain the database, report metrics, update project options on the user form and a search/ update and close the user form once it has been completed. The Admin portion is password protected for limited amount of user interference. I had something similar in Access and now have created it in Excel. This works great on my desktop but when I go to share it... Just nothing but problems. Sometimes it works great with one other using it but when multiple users, saves, attempts it becomes a problem. Auto numbering one of the issues. This has all become a problem the more companies have gone to cloud base apps instead of a local shared drive. Cloud base apps are a pain for this type of solution. I have no problem learning/ creating this in a different environment/ app if need be but what is the best way to create a macro-enabled type excel/access database with user forms, charts, metrics, tables, etc. for sharing and all cloud based.. like SharePoint and OneDrive?
Macro enabled excel files will not work properly in SharePoint or OneDrive, there will be too many conflicts.
Office Addin will be my choice, this way the database will be in a single place, all users will operate the same database.
Not familiar with this option. Where would the file be located. How would it be shared?
Will it try to use the online/ web version? I heard macros do not work with web/ online version of excel.
An Office addin is in fact an embedded website inside excel, in a side panel.
The code is on server, not in the addin.
Anyway, it's a more complex but more flexible solution, requires a server/host, coding is in .Net, or javascript, or any language preferred for a website. Office.js javascript library from microsoft is also used.
If you have a M365 license, you can write instead Automate scripts (typescript), that will work in browser, in sharepoint:
You can find a good tutorial on our site:
https://www.myonlinetraininghub.com/write-excel-office-scripts-with-chatgpt
This way, all users will work in browser in that excel file, browser sync between users is much better.
