0

Hi:

I have a workbook that has several sheets. One sheet called FORM is password protected (by me). I have a macro that clears all sheets but when it gets to FORM it prompts for the password (which I don't wan't to give to my users).

Is there a way to pass the password to the sheet as the macro is running?

thanks

3
Contributors
2
Replies
3
Views
12 Years
Discussion Span
Last Post by vba_dev
0

Hi:

I have a workbook that has several sheets. One sheet called FORM is password protected (by me). I have a macro that clears all sheets but when it gets to FORM it prompts for the password (which I don't wan't to give to my users).

Is there a way to pass the password to the sheet as the macro is running?

thanks

See below. You also need to protect the VBA module so the user can't see the password:
In project explorer, right click on the modue and select "[module name] properties...". Then click on the protect tab and check lock project for viewing and add a password.

Change yourpassword with any password you want.

' Unprotect the FORM sheet so that VBA can write to it
Sheets("FORM").Unprotect Password:="yourpassword"

' Protect the FORM sheet (change the options after the password to suit your needs)
Sheets("FORM ").Protect Password:="yourpassword", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True

0

This simple code will solve your problem.
I assume that sheet1 is your form sheet which you want to protect and only access through code.

sheet1.protect "password", userinterfaceonly:=true

Please note that sheet1 in given in "Microsoft Excel Object" Index in Visual Basic Editor.

Visit my blog if you are willing to download an ebook on Excel.
http://total-ebooks4free.blogspot.com/

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.