Userform to change password in VBE

[CaptRon]

I've created a workbook that allows the user to access the data entry sections by entering a password into a userform. Those without that permission are confined to view data only. I've used the same simple password throughout the workbook, not different passwords in each of the macros where needed.

I'd like to be able to devise a method that would permit the office manager to readily change that single password in all the code without having to go into the VBE and locate each instance of it. I don't even want them in the VBE at all, if possible. Don't change the oil, just drive, if you get my meaning.

I wonder if it is possible to make a change like this with a userform. Any ideas?

Thanks.

Ron

[mdmackillop]

Are you working in a Server setup? My initial thought is to store user names and passwords in a text file and read in the password from that file. If you want to keep it on each PC, look at SaveSettings/GetSettings, where you can store data in the registry. Check the KB for an item on this.

[rory]

Depends how secure you need it to be (relatively speaking, given how inherently insecure Excel passwords are). You could store the password in a hidden workbook name and use that in your code. At the very least, you should not be hardcoding the password in multiple locations in the code - declare a constant and refer to that in your code; then you only have to change the password in one location.

[Bob Phillips]

I don't think this meets Ron's criteria of ... I'd like to be able to devise a method that would permit the office manager to readily change that single password in all the code without having to go into the VBE and locate each instance.

It looks that he wants the password away from Excle/VBA, in a simple to change place. MD seemed to answer that perfectly.

[rory]

You could store the password in a hidden workbook name and use that in your code

Seems on point to me. The rest was merely addressing my inference that the password was hardcoded in multiple places.

[CaptRon]

Rory,

I do need to declare that password once. I should have thought of that. I do that with values in formulas. I suppose I would place that in This Workbook. It has been easy enough for me to do using the replace function on the toolbar applied to the whole project ("oldpswrd" replaced with "newpswrd"). Could you suggest some code that would properly declare that password?

Anyway, that's half the solution. Being able to change that password is the other half. The file will reside on a P drive on our server so everyone in the office can access it but we only want assigned individuals to have the ability to manipulate the data. The office manager may wish to alter the password from time to time and I may be fishing.

MD, please tell me more about the method you suggested and where I might go for more info to learn about it. I don't expect to be spoon fed everything here. I appreciate the help and direction from all.

Ron

[unmarkedhelicopter]

I'd go with MD's suggestion but I'd make sure the store password was encoded, you take the submitted text, encode that and if it matches the stored version, allow the code to run.
Obviously you need to use one way encryption otherwise they will just reverse engineer it.

[Bob Phillips]

File permissions should be sufficient.

[mdmackillop]

This users the txt file method and the UserName/Password in the file.

[CaptRon]

Hello MD,

I tried your TestProtect. Is the .txt supposed to update when you enter something in the new password userform? Or do I go into the txt file and enter the info there?

Ron

[mdmackillop]

The userform will update the text file, or you can edit it directly.