Change password to open excel file programatically

One thing I can thing of, is to create a macro called up in Open event. The macro displays input box and ask the user to type a password. If the password matches whatever in cell A1 (for example) then the file open.

If I understand your request properly, what you are looking for is a way to protect the file contents from being viewed (hence the password to open the file) but once that password is supplied, there will be no restrictions on modifying the file.

If this is the case, you will likely need to use an approach something like the following:

1) Setup your workbook to force the user to enable macros upon opening the workbook. See the article Force users to enable macros in a workbook by Ken Puls for one way in which to do this. There are probably several other viable alternatives to force enabling of macros out there.

2) Modify the WorkbookOpen event such that when the user opens the workbook, your code displays an input box requesting the required password to open the file. If the correct password is supplied, have your code continue with the ShowAllSheets subroutine (referenced in Ken Puls method).

3) Once the password is validated, continue with the remainder of the process for unhiding the various sheets in the workbook.

4) You will probably want the welcome page to have a button on it that the user can click that will also pull up the password input box. This will enable the user to try again if he or she entered the wrong password upon the initial opening of the workbook.

I already use a similar approach to force the enabling of macros in a couple of my projects, however, I do not require a password to open the file in those scenarios. But what I listed above is how I would approach it.

Hopefully this is useful.

If I helped, please click the star below the post to add to my reputation.