Suppress Protection Prompt

[neodjandre]

Is there a macro to supress the popup prompt saying "the cell is protected...." when users double click on a protected cell ?

i prefer for nothing to happen when users double click a protected cell..

 

[Jaafar Tribak]

If you just want to get rid of the prompt when doubleclicking then i guess you can use this in the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Regards.

 

[neodjandre]

that's true .. but will this code work when users want to double click to edit cells which are not protected ??

 

[neodjandre]

could you not amend the code to say:

If cell is a formula then "Cancel = TRUE" otherwise edit cell when users double click on a cell

?

 

[Legacy 98055]

You can forbid selection in locked cells all together.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Worksheet_Activate()
       Me.EnableSelection = xlUnlockedCells
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("102620079273906").value=document.all("102620079273906").value.replace(/<br \/>\s\s/g,"");document.all("102620079273906").value=document.all("102620079273906").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("102620079273906").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="102620079273906" wrap="virtual">
Private Sub Worksheet_Activate()
Me.EnableSelection = xlUnlockedCells
End Sub</textarea>

 

[neodjandre]

thanks right click .. can i use this as a workbook event ?

 

[dave3009]

Hi

I may have got the wrong end of the stick here but can you not just uncheck 'Select Locked Cells' when protecting the sheet, this will mean that the cell cannot be selected thus bypassing the unwanted error message.

HTH

Dave

 

[Legacy 98055]

No Dave. You have the right end of the correct stick. We simply take pleasure in over-complicating things.

 

[neodjandre]

nice

 

[neodjandre]

however, the bad thing about

Private Sub Worksheet_Activate()
Me.EnableSelection = xlUnlockedCells
End Sub

is that you cannot select and copy locked cells..

can we not have a macro along the lines:

before double click

if selected cell is a formula then "cancel = True"

otherwise

do nothing (i.e. when you double click you are able to edit the cell)

??