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

'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

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

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