Change paste to paste special
 

I tried this before, but I can't get things to work the way I want. PROBABLY
because I didn't explain things correctly.

I want to declare a range of cells (K18:K37), on sheet 1, the active spread
sheet, so that when a user clicks on one of these cells, and clicks paste, it
will actually paste special as a value. (The same as if the user clicked
"paste special" and then "value".

Thanks for any help you can give.
--
Howard

Change paste to paste special
 

'Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K18:K37")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next

'Check to see if a paste action is occuring
If Application.CutCopyMode = xlCopy Or _
Application.CutCopyMode = xlCut Then

Application.Undo
Selection.PasteSpecial Paste:=xlPasteValues
End If

Application.EnableEvents = True

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Howard" wrote:

I tried this before, but I can't get things to work the way I want. PROBABLY
because I didn't explain things correctly.

I want to declare a range of cells (K18:K37), on sheet 1, the active spread
sheet, so that when a user clicks on one of these cells, and clicks paste, it
will actually paste special as a value. (The same as if the user clicked
"paste special" and then "value".

Thanks for any help you can give.
--
Howard

Change paste to paste special
 

Howard,

Maaybe this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("K18:K37")) Is Nothing Then
Application.EnableEvents = False
For Each c In Target
If c.HasFormula Then
Target.Value = UCase(Target.Value)
End If
Next
Application.EnableEvents = True
End If
End Sub


Mike

"Howard" wrote:

I tried this before, but I can't get things to work the way I want. PROBABLY
because I didn't explain things correctly.

I want to declare a range of cells (K18:K37), on sheet 1, the active spread
sheet, so that when a user clicks on one of these cells, and clicks paste, it
will actually paste special as a value. (The same as if the user clicked
"paste special" and then "value".

Thanks for any help you can give.
--
Howard

Change paste to paste special
 

Thanks Luke:
I'll try this, but it looks like what I'm looking for.
Thanks again.
--
Howard


"Luke M" wrote:

'Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("K18:K37")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next

'Check to see if a paste action is occuring
If Application.CutCopyMode = xlCopy Or _
Application.CutCopyMode = xlCut Then

Application.Undo
Selection.PasteSpecial Paste:=xlPasteValues
End If

Application.EnableEvents = True

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Howard" wrote:

I tried this before, but I can't get things to work the way I want. PROBABLY
because I didn't explain things correctly.

I want to declare a range of cells (K18:K37), on sheet 1, the active spread
sheet, so that when a user clicks on one of these cells, and clicks paste, it
will actually paste special as a value. (The same as if the user clicked
"paste special" and then "value".

Thanks for any help you can give.
--
Howard