I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Carl,

Typically, the user does change the _selection_ after they copy, to move to the paste area. My code
is not triggered by the change event, but by the selection change event.

Note that you can 'globalize' this routine by using the workbooks' worksheet selection change event
(Workbook_SheetSelectionChange)

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Thanks again, Bernie. You are correct the user would change the selection
after they copy to the clipboard. I entered the code as a worksheet macro
exactly as you provided but when I move to another cell it allows me to paste
into that cell and only clears the clipboard after I make a change in the
selected cell (which is too late). Thanks for your patience. Carl

"Bernie Deitrick" wrote:

Carl,

Typically, the user does change the _selection_ after they copy, to move to the paste area. My code
is not triggered by the change event, but by the selection change event.

Note that you can 'globalize' this routine by using the workbooks' worksheet selection change event
(Workbook_SheetSelectionChange)

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Carl,

I'm sorry - I read your post, but come away with Data Validation in my brain and not Conditional
Formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells(1).FormatConditions.Count = 0 Then Exit Sub
Application.CutCopyMode = False
End Sub

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks again, Bernie. You are correct the user would change the selection
after they copy to the clipboard. I entered the code as a worksheet macro
exactly as you provided but when I move to another cell it allows me to paste
into that cell and only clears the clipboard after I make a change in the
selected cell (which is too late). Thanks for your patience. Carl

"Bernie Deitrick" wrote:

Carl,

Typically, the user does change the _selection_ after they copy, to move to the paste area. My
code
is not triggered by the change event, but by the selection change event.

Note that you can 'globalize' this routine by using the workbooks' worksheet selection change
event
(Workbook_SheetSelectionChange)

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into
the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

No problem, thanks. I came up with the following but it only works when I
double-click on the cell and most users will single-click before attempting
to paste. Is there a way to make it select cell and run the code on
single-click? Thanks for your patience, Carl

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If Application.CutCopyMode = False Then
Cancel = True
Else
Application.CutCopyMode = False
End If
End With
End Sub

"Bernie Deitrick" wrote:

Carl,

I'm sorry - I read your post, but come away with Data Validation in my brain and not Conditional
Formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells(1).FormatConditions.Count = 0 Then Exit Sub
Application.CutCopyMode = False
End Sub

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks again, Bernie. You are correct the user would change the selection
after they copy to the clipboard. I entered the code as a worksheet macro
exactly as you provided but when I move to another cell it allows me to paste
into that cell and only clears the clipboard after I make a change in the
selected cell (which is too late). Thanks for your patience. Carl

"Bernie Deitrick" wrote:

Carl,

Typically, the user does change the _selection_ after they copy, to move to the paste area. My
code
is not triggered by the change event, but by the selection change event.

Note that you can 'globalize' this routine by using the workbooks' worksheet selection change
event
(Workbook_SheetSelectionChange)

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into
the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl

Thanks for the help, Bernie. The following accomplished my goal with a popup
message warning the user not to paste data from one cell to the next but with
a way to disable it (I can enter a "1" in cell "A1" to allow editing without
a nuisance alarm). To prevent user from ignoring the message and pasting
anyway, just replace [Application.Run "PopupMsgDoNotCopyPaste"] with
[Application.CutCopyMode = False]. Thanks again to you and Tom Ogilvy, Carl

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Or Sheets("Summary
Sheet").Range("a1") = 1 Then
Cancel = True
Else
Application.Run "PopupMsgDoNotCopyPaste"
End If
End Sub

"Carl" wrote:

No problem, thanks. I came up with the following but it only works when I
double-click on the cell and most users will single-click before attempting
to paste. Is there a way to make it select cell and run the code on
single-click? Thanks for your patience, Carl

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If Application.CutCopyMode = False Then
Cancel = True
Else
Application.CutCopyMode = False
End If
End With
End Sub

"Bernie Deitrick" wrote:

Carl,

I'm sorry - I read your post, but come away with Data Validation in my brain and not Conditional
Formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells(1).FormatConditions.Count = 0 Then Exit Sub
Application.CutCopyMode = False
End Sub

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks again, Bernie. You are correct the user would change the selection
after they copy to the clipboard. I entered the code as a worksheet macro
exactly as you provided but when I move to another cell it allows me to paste
into that cell and only clears the clipboard after I make a change in the
selected cell (which is too late). Thanks for your patience. Carl

"Bernie Deitrick" wrote:

Carl,

Typically, the user does change the _selection_ after they copy, to move to the paste area. My
code
is not triggered by the change event, but by the selection change event.

Note that you can 'globalize' this routine by using the workbooks' worksheet selection change
event
(Workbook_SheetSelectionChange)

HTH,
Bernie
MS Excel MVP


"Carl" wrote in message
...
Thanks, Bernie.
Your code worked perfectly to clear the clipboard when a change was made on
the worksheet. However, it does not stop someone from cutting/pasting from
one cell to the next since they typically don't change the worksheet between
steps. Is there a way to have Excel execute a code (popup msg, etc.) anytime
someone puts something on the clipboard? This would take care of the problem
without restricting them all pasting. Thanks again, Carl

"Bernie Deitrick" wrote:

Carl,

Copy the code below, right-click the sheet tab and select "View Code" and paste the code into
the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myType As Long
On Error GoTo noValidation
myType = Target.Cells(1).Validation.Type
Application.CutCopyMode = False
noValidation:
End Sub






"Carl" wrote in message
...
I have a problem in that users sometimes cut and past data from one cell to
the next in my worksheets and this wrecks the conditional formatting and
formulas. I can't protect the cells because they need to be able to make
entries and I can't allow specific users only because the users are always
changing. Is there a way to enter a worksheet macro that will clear the
clipboard before the paste command is run, thus preventing the activitiy?
Thanks in advance, Carl