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
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
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/
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.