Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change paste to only paste values
I have a spreadsheet that is nice and formatted just the way I want it
to be, but the people using the spreadsheet are mainly copying and pasting data from another spreadsheet into the target spreadsheet, and they rarely remember to "paste special" --- "values" to preserve the formatting. I've tried to protect the formatting via protect worksheet, but that seems to get overridden if the values are pasted in. So now, I'm trying to devise a way to use VB to make all paste operations default to paste only values. I saw this code, Sub New_paste() ActiveCell.PasteSpecial XlValues End Sub, on the web, but it doesn't do anything, and I don't know much about VB, but I do know how to get into the VB editor. Is there an easy way to make all paste operations (shortcut on toolbar, right-click menu, edit menu, ctrl+v) default to paste values only? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change paste to only paste values
It actually looks like this does the trick:
Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim myValue With Application ..EnableEvents = False myValue = Target.Value ..Undo Target = myValue ..EnableEvents = True End With End Sub If anyone has a better suggestion, let me know. The only downfall i see is the inability to "Undo" after the Paste operation is complete. Chris On Nov 6, 1:39*pm, Chris Trygstad wrote: I have a spreadsheet that is nice and formatted just the way I want it to be, but the people using the spreadsheet are mainly copying and pasting data from another spreadsheet into the target spreadsheet, and they rarely remember to "paste special" --- "values" to preserve the formatting. I've tried to protect the formatting via protect worksheet, but that seems to get overridden if the values are pasted in. So now, I'm trying to devise a way to use VB to make all paste operations default to paste only values. I saw this code, Sub New_paste() ActiveCell.PasteSpecial XlValues End Sub, on the web, but it doesn't do anything, and I don't know much about VB, but I do know how to get into the VB editor. Is there an easy way to make all paste operations (shortcut on toolbar, right-click menu, edit menu, ctrl+v) default to paste values only? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL PASTE DOES NOT CHANGE DATA - PASTE DOESN'T WORK! | Excel Worksheet Functions | |||
can you change the default paste method? (paste values) | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
to find change and paste existing values/rows in excel with help of form | Excel Discussion (Misc queries) | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |