How do you disable cut, copy & paste for multiple cells?

[Quick?]

How do you disable cut, copy & paste for multiple cells?

In some sample code I found in this forum it displayed:
-----------------------------------------------------------------------
'*** In the ThisWorkbook Module ***
Option Explicit

Private Sub Workbook_Activate()
'Force the current selection to be selected, triggering the appropriate
'state of the cut, copy & paste commands
Selection.Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Re-enable the cut, copy & paste commands
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
'Re-enable the cut, copy & paste commands
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
'Force the current selection to be selected, triggering the appropriate
'state of the cut, copy & paste commands
Selection.Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Toggle the cut, copy & paste commands on selected ranges

Select Case Sh.Name
Case Is = "Sheet1"
'Disable cut, copy & paste for Sheet1, Column A
If Not Intersect(Target, Target.Parent.Columns(1)) Is Nothing Then
Call ToggleCutCopyAndPaste(False)
Else
Call ToggleCutCopyAndPaste(True)
End If

Case Is = "Sheet2"
'Disable cut, copy & paste for Sheet2, Range G1:H5
If Not Intersect(Target, Target.Parent.Range("G1:H5")) Is Nothing Then
Call ToggleCutCopyAndPaste(False)
Else
Call ToggleCutCopyAndPaste(True)
End If

Case Else
'Re-enable cut copy and paste commands as this is not a restricted sheet
Call ToggleCutCopyAndPaste(True)
End Select
End Sub
-----------------------------------------------------------------------


But how would I disable multiple Cells (A2,D21,C47, C62:F62,.....) and not a single Range in the example above????


Please PLEASE help if you can.

Regards,

K
:banghead: : pray2: :doh:

[GTO]

Greetings K,

First, let me say :hi: "Welcome!":hi: and relay that you will meet some very nice folks here.

Now as to your question, a quick answer would be that mutiple cells can be a single (non-contiguous) range. For a better answer, please hold off about a day and check and/or bump the thread if I forget.

I am checking one tiny issue, and would rather not give an answer that needs followed up because of a tiny 'hiccup' I may have spotted.

Hopefully that makes sense, as I'd like to help,

Mark

[Quick?]

Hello Mark,

Have you been able to check on that 'hiccup' to see if you could find a solution.

Thanks for your help in advance.

Regards,

K

[GTO]

For anyone interested in this type of procedure, I would include:

K's question relates to thread: http://vbaexpress.com/forum/showthread.php?t=19391

...wherein Ken Puls delineates further adapability to disabling/re-enabling cut/copy/paste capabilities, as originally layed out in Ken's KB entry, which is at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

=======================================================

Hi K,

Yes - sorry about that. I have attached a sample workbook, based upon Ken's original work, inclusive of the updates he provided Shannon (#19391). I believe this may help you understand ranges a bit clearer.

In short, when we look at:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Toggle the cut, copy & paste commands on selected ranges
Select Case Sh.Name
Case Is = "Sheet1"
'Disable cut, copy & paste for Sheet1, Column A
If Not Intersect(Target, Target.Parent.Columns(1)) Is Nothing Then
Call ToggleCutCopyAndPaste(False)
Else
Call ToggleCutCopyAndPaste(True)
End If
Case Is = "Sheet2"
'Disable cut, copy & paste for Sheet2, Range G1:H5
If Not Intersect(Target, Target.Parent.Range("G1:H5")) Is Nothing Then
Call ToggleCutCopyAndPaste(False)
Else
Call ToggleCutCopyAndPaste(True)
End If
Case Is = "Sheet3"
'// Disable cut, copy & paste for Sheet3, cells as indicated //
If Not Intersect(Target, Target.Parent.Range("A2, D21, C47, C62:F62")) _
Is Nothing Then
Call ToggleCutCopyAndPaste(False)
Else
Call ToggleCutCopyAndPaste(True)
End If
Case Else
'Re-enable cut copy and paste commands as this is not a restricted sheet
Call ToggleCutCopyAndPaste(True)
End Select
End Sub

...these are all testing against ranges, whether the range is an entire column, "G1:H5" or Range("A2, D21, C47, C62:F62")).

See a range could be just one cell or multiple cells in a contiguous range, or in a non-contiguous range. Does that make better sense?

As to the 'hiccup': I believe that the act of setting .CellDragAndDrop to True cancels copy or cut mode. This would not have been apparent (and moot) when the changes in allowing CutCopy&Paste were only happening when switching wb's.

Thus - I only added a test, to wit:

If Not Application.CellDragAndDrop _
Or Not Allow Then
Application.CellDragAndDrop = Allow
End If

Hope this helps,

Mark