Error with Protect and Unprotect through VBA

Hello all,


I have an error while protect and unprotect through vba where some options are missing while mouse right click.When you protect without vba for four columns its fine but when protect using vba the options delete,paste,insert copied cells are missing .Please help me.

Please find the attachments one with Excel VBA and other by selecting protect and unprotect in excel manually.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Unprotect
    ActiveSheet.Protect _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=True, _
    UserInterfaceOnly:=True, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=True
End Sub

---

Thanks & regards
Sampath

Have you tried simply recording the .Protect part? I get:

Code:

---

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True

---

I particularly note that the AllowUserInterfaceOnly argument is not included. You have included it in your code, and I would suspect (not tested though) that this would block the other options from being effective.

Hope that helps,

Mark

Hello GTO,

Thanks for your quick reply.I tried by recording the macro and the same piece of code i kept in Worksheet_SelectionChange but not worked out i tried all the combinations noting worked thats why i approached forum for help.Once again thanks for reply .If you have any solution please share me.

Quote:

---

Originally Posted by msampath

...When you protect without vba for four columns its fine but when protect using vba the options delete,paste,insert copied cells are missing ...

---

Okay then, can you list exactly which options you select manually when protecting the sheet that end up with the desired results? Also, what version of Excel are you using?

Actually when i protect sheet manually by clicking protect() i checked these options [select locked cells ,select unlocked cells ,Insert columns,Delete columns] where i got options when right click copy these are options available which are attached in above screenshot(ProtectfromExcel.jpg) .I should acheive same options when i do with VBA Unprotect and Protect sheet.If you are not clear please tell me .

Thanks for your reply !