Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet change macro for paste special | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
How do I capture user paste action and convert to Paste Special | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |