Open from code with password to modify 1

[MrsTFB]

Hey guys,
I want to place password to modify on a workbook, Excel97. I want to however, be able to write some code that will allow my user to open the workbook, write data to it from a form I have built, the close the workbook from the form and it will save it. Here's what i've written, but it still prompts for the password. I wanted it to enter it from the code. Is this not possible?

Thanks for any help in advance.
MrsTFB in Tennessee

Workbooks.Open FileName:="\\APCOM_1\SYS\GROUP\QA\EXCEL97\97SQC\97DATA\WH9HC.XLS", UpdateLinks:=0, PASSWORD:="rae"

 

[MrsTFB]

It's me, I should have added that if the workbook is gone to manually, I do not want them to be able to do anything other than view it. That's the reason for the password.

Thanks again,
MrsTFB in Tennessee

 

[MrsTFB]

Help!
I don't think this is hard, it's just not something that I know how to do.

A true guru, which I know is here, can easily help me here.

Waiting on answer, Thanks in advance.

If it's not possible, someone please tell me so I can think of another method.

MrsTFB in Tennessee

 

[VulcanJedi]

I think this still needs some clarification for anyone to help resolve. Be explicit for what you are asking...I for one still don't understand the need for a innate password within the code? If its provided in the code, why is it necessary at all? I just don't understand the need for the pwrd and I guess more importantly how it relates to the use of the file, otherwise I don't the solution is far from reach?
as I understand: You open the file, it auto retains the coded password, user interfaces with form, closes/saves. again now what? Tranpkp
************************************
- Let me know if this helped/worked!

 

[kylua]

If the workbook is saved as needing a password for updating, this is referenced to differently to a password to stop the workbook being opened.

Instead of password:="Rae"

use

Writerespassword:="Rae"

Should sort it.

I would however just have the workbook and worksheet password protected as the file can be easily resaved under another name with no protection, the original deleted and the copy renamed to the original's name.
Or maybe I'm just paranoid.

 

[MrsTFB]

I'm sorry I'm not clear. The workbook needs to be password protected because some users are ignorant about Excel and easily make mistakes in it. However, for that reason I have built a VBA form for the data entry and technically they are never supposed to even look at the workbook directly, simply open the workbook, which displays the form, enter the information, close the workbook from the form which saves it. However, some of our know-it-alls, (who don't) have gone in to "fix" things and messed some things up. I've gotten it all corrected, so I want to password it for simple opening, however when the macro is used for data entry, the workbook will need to be modified and saved. The alternate names would cause mass confusion, although I appreciate your reply.

I hope this clears it up. I will try the WriteRes and see if that is the simple answer.

Thanks again,
mrsTFB in Tennessee

 

[rudo]

Hi,

I suppose the following method let your users read, but they will have to use your form if they want to save data.

Save WH9HC.xls with read- and writeprotection.
Give the user the password for reading.
When the form is activated, close the read-only copy and open the workbook with read-only:=false and specify Password and WriteResPassword

You must probably avoid to put the code for the form in Workbook WH9HC.xls. (As one copy closes and another gets active.) You could put, for instance, its code in Workbook Personnal, which is always active.

By the way, if you do not want to confront the user with a read-only password for this file, you can put an Excel-file on his/her desktop. In your case it would be called "Towards WH9HC.xls" In the Workbook Open macro of this file, you could write the following code:

Private Sub Workbook_Open()

ChDir "\\Apcom\....."
Application.DisplayAlerts = False
Workbooks.Open Filename:="\\Apcom\...\WH9HC.xls" ReadOnly:=True, Password:"read"
Application.DisplayAlerts = True

I hope this suits your needs. If you want more code, let me know.

Greetings from Holland,

Rudo

 

[rmikesmith]

MrsTFB,

Here is another suggestion. If your primary concern is preventing tampering of the worksheet(s) then I would protect the worksheet(s) using the following bit of code:

Worksheets("SheetName").Protect Password:="YourPW", UserInterfaceOnly:=True

The key is the UserInterfaceOnly parameter. Setting this to True allows your VBA (macro) code to manipulate the worksheet cells while maintaining protection against manual changes. This option is not available from the user interface so needs to be activated through code. As long as you don't UnProtect the worksheet(s), the above code only needs to be run once and doesn't even need to be included in the workbook.

HTH
Mike

 

[rudo]

rudo to rmikesmth

Thanks for the suggestion. Didn't know this method. Will save me quite a lot of work too.