Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I default to Paste Special?
I have created a spreadsheet to be used by my staff for inputing data. Many
of the cells have formulas in them that are protected. However, occasionally my staff will cut and paste or copy and paste cells from one row or colum to another, creating errors in the spreadsheet becuase the formula follows. Is there a way to automatically have the sheet use Paste=Special when the copy=paste function is used? Thanks in advance to anyone who may have some input on this... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I default to Paste Special?
Hi
AFAIK not possible. But you could record a macro while using paste special and assign a shortcut for this macro -- Regards Frank Kabel Frankfurt, Germany "Flop" schrieb im Newsbeitrag ... I have created a spreadsheet to be used by my staff for inputing data. Many of the cells have formulas in them that are protected. However, occasionally my staff will cut and paste or copy and paste cells from one row or colum to another, creating errors in the spreadsheet becuase the formula follows. Is there a way to automatically have the sheet use Paste=Special when the copy=paste function is used? Thanks in advance to anyone who may have some input on this... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I default to Paste Special?
You could actually use a worksheet change event to create your formula
values. Then, there is no formula left to copy, just values. Right click sheet tabview codecopy this example. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Or Target.Row < 2 Then Exit Sub Target.Offset(0, 1) = Target * 3.34 End Sub -- Don Guillett SalesAid Software "Flop" wrote in message ... I have created a spreadsheet to be used by my staff for inputing data. Many of the cells have formulas in them that are protected. However, occasionally my staff will cut and paste or copy and paste cells from one row or colum to another, creating errors in the spreadsheet becuase the formula follows. Is there a way to automatically have the sheet use Paste=Special when the copy=paste function is used? Thanks in advance to anyone who may have some input on this... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I default to Paste Special?
Couple of ideas for you. Neither very satisfactory and wouldn't want them
myself, but maybe of use for you. Paste into the ThisWorkbook module Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim retval If Application.CutCopyMode < False Then '''''''''''''''' '''''method 1 'retval = Application.Dialogs(xlDialogPasteSpecial).Show(3) 'If retval = False Then Application.CutCopyMode = False '''''''''''''''' ''''''''''''''' '''''Method 2 retval = MsgBox("Yes: Paste values & Formats" & vbCr & _ "No: Paste values only", vbYesNoCancel, _ "Paste special No Undo") If retval < vbCancel Then Selection.PasteSpecial Paste:=xlValues If retval = vbYes Then Selection.PasteSpecial Paste:=xlFormats End If Else SendKeys "{Esc}", True End If ''''''''''''''''' End If End Sub Regards, Peter "Flop" wrote in message ... I have created a spreadsheet to be used by my staff for inputing data. Many of the cells have formulas in them that are protected. However, occasionally my staff will cut and paste or copy and paste cells from one row or colum to another, creating errors in the spreadsheet becuase the formula follows. Is there a way to automatically have the sheet use Paste=Special when the copy=paste function is used? Thanks in advance to anyone who may have some input on this... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
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 |