Insert line on Protected sheet Error

[TimRHSD]

Hi,

I am inserting a line with the following code, but I still get an error message even though I have .displayalerts = False.

Any ideas or suggestions.

Thanks, Tim

Private Sub WorkSheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Application.DisplayAlerts = False
    ActiveSheet.Unprotect password:="elephant"
    Selection.EntireRow.Insert Shift:=xlDown
    ActiveSheet.Protect password:="elephant"
   'Application.DisplayAlerts = True  'commenting out didn't work
End Sub

Tim Rutherford

http://www.Manna-Consulting.com

 

[xlbo]

what line do you get the error message on - note - displayalerts = false only stops alerts like "Are you sure you want to delete this sheet" - it doesn't stop error messages - to do that you need an error handling procedure

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TimRHSD]

XLBO,

The error I am receiving is, "The cell or chart you are trying to to change is protected...", but this message occurs AFTER the event. I insert the row and protect the sheet again after that. Where can I prevent this message?

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[xlbo]

Sorry - I'm not quite understanding....are you saying that the procedure works but you get the error message ???

In that case, just use:

Private Sub WorkSheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    [COLOR=green]'Set up error checking[/color]
    [b]on error resume next[/b]
    ActiveSheet.Unprotect password:="elephant"
    Selection.EntireRow.Insert Shift:=xlDown
    ActiveSheet.Protect password:="elephant"
    [COLOR=green]'set error checking back to normal[/color]
    [b]on error goto 0[/b]
  End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ChrisBurch]

Hi Tim,

No solution for you, but I can confirm your issue. I tried your code and get the following sequence:-

Double-click a cell
worksheet protection is removed
line is inserted
protection is re-activated
Error message re 'cell is protected etc'

The error message comes after the protection has been re-activated and prior to exiting the sub.

Sorry I could'nt help further.

Chris

IT would be the perfect job......if it didn't have users!!!

 

[xlbo]

Ok - I can explain why as well. the event fires BEFORE the double click. Protection is turned off and then on again BUT after the sub has run, the worksheet is protected again. By double clicking in the cell, you are telling excel that you are trying to EDIT the cell - the cell has been re-protected so you get the error message

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question