Solved worksheet protection using vba

June 26, 2013 at 12:49:46
Specs: Windows 7
I know have a number of worksheets in a workbook that are protected so that they are read only
I have some worksheets that information can only be added to certain cells
Some of these worksheets have VBA's that then apply information to the read only worksheets
At this present time I have to enter the password each time when a protected sheet is to have data posted to it, and when leaving that worksheet, enter the password again to re-apply the protection

Is there an instruction that can be added to a VBA to firstly remove the protection, and then re-apply the protection

The password is generic to all worksheets

Looking forward to some pointers on this

See More: worksheet protection using vba

Report •

June 26, 2013 at 13:15:55
Lock all of the protected sheets from VBA, and pass True to UserInterfaceOnly. Then remove any reference to protecting or unprotecting from your code.
Your sheet locking function would probably look something like this:
Sub LockSheets()
  sheetsToLock = Array("Sheet1", "Sheet3")
  Dim ws As Worksheet
  For Each s In sheetsToLock
    ActiveWorkbook.sheets(s).Protect Password:="somePassword", _
  Next s
End Sub

How To Ask Questions The Smart Way

Report •

June 26, 2013 at 13:25:21
✔ Best Answer
If you hardcode the sheet level password into VBA, you will then want to set the properties of the VBAProject to "Lock Project For Viewing" and then set a password on the VBAProject.

Otherwise anyone who opens the VBA editor will be able to see the sheet level password and defeat it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 26, 2013 at 13:26:42
Thanks for responding but I have found a posting going back to June 2010 by DerbyDad03, that with a bit of manipulation seems to do the trick. I know have the following scenario

Sheet 1 Protected other then cells where data is entered
Sheet 2 Protected - read only

Before when I entered data and then ran the Vba, I was prompted for a password to unlock Sheet2

With the following line of instruction

Sheets("Sheet2").Unprotect Password:="sillyboy"

this removes the password, and then to protect the sheet

Sheets("Sheet2").Protect Password:="sillyboy", DrawingObjects:=True, Contents:=True, Scenarios:=True

Report •

Related Solutions

Ask Question