Change paste to paste special

[Howard]

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.

 

[Luke M]

'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

 

[Mike H]

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]

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