Storing data in an add-in for use later

[Bythsx-Addagio]

Hello,
I have an add-in which multiple users use. Some of the features involve
querying a SQL Database which requires a password. I would like to be able
to code the add-in to prompt the user for their password and store it
somewhere so it will be available the next time the add-in is used. Does
anyone have any suggestions on how best to achieve this?

Thanks,
b

 

[Jim Thomlinson]

What you are asking is reasonably straight forward. In your project and a
module to store global variables. In that module define a user name variable
and a password variable.

Add a user form which prompts the user for their name and password. The user
form should have a command button that connects and disconnects from the
database to confirm that the name and password are valid. If so then store
the name and password in the globals.

I assume that your addin has some kind of toolbar or menu for the user to
interact with. When the user tries to interact with the database the first
thing it does is checks for the global username and password. If one exists
then it does it's business. If not then it shows the user form...

 

[Bythsx-Addagio]

Thanks Jim. Actually I want to add to that and set it so that after the user
shuts down, completely restarts their computer etc. and opens excel again the
add-in will load and I would like the user/pass to still be accessible. I
figure this will require saving to a sheet in the add-ins workbook. I also
would like to figure out how to password protect the add-in so no one can
open it in the editor and view the stored password.

What do you think? Is this a crazy idea?

 

[paul.robinson]

Hi
you could save it as a name in Thisworkbook (i.e. the AddIn).

Sub test()
ThisWorkbook.Names.Add "myPassword", "junk", xlHidden
PWordinSQL = [myPassword]
msgbox PWordinSQL
End Sub

The actual password could come from a textbox e.g
userform1.Textbox1.Value.
The notation [myPassword] evaluates myPassword to give "junk", the
actual password. Setting xlHidden means the name won't appear in the
Names collection.


To protect an AddIn use Tools, VBAProject Properties, Protection
regards
Paul