More often than not we are concerned with the safety of the macro instead of the users using the macro. So even though we set passwords for locking the codes of the macro, anyone who has the add-in is able to use the macro even if it is not intended for them. This is bad business strategy! Imagine creating a macro for Client A, which is now being used by Client B, C, D, and F, just because they have access to the add-in (or .xlam, ,dotm, or .ppam file for Excel, Word, and PowerPoint respectively). You are losing business.
But what if you can set username and passwords for individual users (or a group of users)? Only users who you have authorized would be able to use that add-in you created. Wouldn't it be great! Now let you make you aware beforehand that this method is not as secure as a Windows or GMail password, but it can do the work. Here's how you can set username and password for login into your MS Office add-in.
Create a separate module inside the macro. Name it RibbonModule (without spaces).
In this module, add in the following piece of code:
Once done, create UserForm1 to help authenticate a set of username and passwords. You can have a separate Excel file containing all the authorized username and passwords. Initially, all the buttons except the login button would be disabled. Once the macro checks from the Excel file and finds a match, only then the rest of the buttons would get enabled. I will write another post on how to do this since it is out of scope for this article. But here's the code you can use to create for UserForm1:
Now save the add-in file. It's time to create the add-in using the Custom UI Editor. Here's the code you need to write in the Custom UI Editor:
If you notice, the login_check does not contains any group ID. What happens is, if the authentication succeeds, the EnabledAllControls function is called by the macro, and all the buttons are enabled irrespective of their tags. If you want you can create granular authentication for each button by giving them different group ID.
