Protecting conditional formating but allowing comments

[Chucky3006]

Hi Guys ruiqure some assistance

I have a spread sheet where multpile users input data on, but with this, they started copy/cutting and pasting breaking the complexes conditional formatting structure

I managed to get the cells, where they should not enter data, locked.

Then managed to get the cells unlocked where the should only enter data "D10:AT114".

Then managed to disable copy/cut and paste by using a VBA code I found online

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***
Option Explicit

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

So here goes the following problem......

I managed to get the sheet exactly protected as i wanted preventing any changes to conditional formatting but here comes the snag:

Now after they enter data they should be able to insert a comment on that cell but when i right click that option no longer exists.


so to sum it up.

I need the sheet to be:

1: Password protected-protecting the cells that no changes are allowed to
2: Allow any user to change data in specified cells "D10:AT114"
3: But conditional formatting should not be altered in cells "D10:AT114".
4: and comments should be possible in cells "A1:AV130.

i would really appreciate assistance....

 

[Jaafar Tribak]

Under the Worksheet Protection dialog, tick the checkbox that says : "Edit Objects" ... That should make the "Insert Comments" option available in the right-click menu of locked as well as unlocked cells.