MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Albert    Date: Oct 29    Category: MS Office    Views: 1939

I have created a workbook with several sheets and various userforms to
input data. I don't want anyone to input data directly, so I want to
protect the sheets, then unprotect them when the userforms are
displayed (then protect them again before leaving the subroutine). I
thought that I could activate the worksheet then put worksheets
("Name").protect or unprotect as necessary. Doesn't work. I must be
missing something obvious. Any ideas?



6 Answers Found

Answer #1    Answered By: Archie Parker     Answered On: Oct 29

ActiveWorkbook.Unprotect "Password"

ActiveWorkbook.Protect "Password"

Answer #2    Answered By: Hamdan Younis     Answered On: Oct 29

I actually tried
ActiveWorksheet.unprotect (I hadn't protected it with a password) but
for some reason it didn't work. Anyway, I now have it working with

ActiveSheet.Protect UserInterfaceOnly:=True

Answer #3    Answered By: Laaibah Malik     Answered On: Oct 29

For anyone interested, I figured out that protect  and unprotect  work
exactly as stated in this email EXCEPT if the sheet is protected from
the spreadsheet command bar before entering the vba module, the
unprotect doesn't work.

Answer #4    Answered By: Daw Boonliang     Answered On: Oct 29

You need to set the EnableSelection property. You are probably protecting the
sheet, but still allowing the users to select both locked & unlocked cells. Tru
something like this:

Sub AAAA()
Call ProtectSht("Sheet1")
End Sub

Sub BBBB()
Call UnprotectSht("Sheet1")
End Sub

Private Sub ProtectSht(ShtName As String)
Sheets(ShtName$).EnableSelection = xlNoSelection
End Sub

Private Sub UnprotectSht(ShtName As String)
Sheets(ShtName$).EnableSelection = xlNoRestriction
End Sub

For greater security, you could add a password.

Answer #5    Answered By: Christie Bradley     Answered On: Oct 29

I actually accomplished what I wanted with the following:

ActiveSheet.Protect UserInterfaceOnly:=True

I don't think there is an issue about locked versus unlocked, because
it works fine now. Before, I had protected two sheets  manually (not
the whole workbook) with no password and the protection worked; then
I had attempted to unprotect  them at the beginning of my routine and
then protect  them again at the end. I have no clue why what I did
before didn't work.

Answer #6    Answered By: Wendy Harrison     Answered On: Oct 29

In that case try this...


"Do something"


Didn't find what you were looking for? Find more on Protect/Unprotect Or get search suggestion and latest updates.