VBA to force paste special

[danielw7]

dear colleagues,
i created a data collection sheet with loads of conditional formattings in order to support the users and to save time for feedback loops. the problem is that copied values overwrite conditional formattings and thus make them useless. i found a script online with which i can force to paste only values by using strg+v and right mouse button + paste. but unfortunately there are a few paste options which are not affected by the script and could cause trouble. the relevant rows in the code are:

Excel.Application.OnKey "^v", m_sPasteProcedure_c
Excel.Application.OnKey "+{INSERT}", m_sPasteProcedure_c

do you have an idea, how i could force paste value for every paste option?

thanks for your help!

best
daniel

[mancubus]

does this help?
http://www.vbaexpress.com/forum/showthread.php?45252-Solved-force-pastespecial-values

or
http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/

[snb]

to 'copy' only values:


Sub M_snb()
sheet2.range("A5")=sheet1.range("A2").value
End Sub

[danielw7]

hey,
thanks for the links. the solution of the second one (EDIT: I mean the solution from Siddharth Rout) seems to fit for my problem, but I always get an error saying "Argument not optional". It seems like I did not declare one or more variables, but I couldn't see it. Here the code:


Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim UndoList As String

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error GoTo Whoa

'~~> Get the undo List to capture the last action performed by user
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

'~~> Check if the last action was not a paste nor an autofill
If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
Then GoTo LetsContinue

'~~> Undo the paste that the user did but we are not clearing
'~~> the clipboard so the copied data is still in memory
Application.Undo

If UndoList = "Auto Fill" Then Selection.Copy

'~~> Do a pastespecial to preserve formats
On Error Resume Next
'~~> Handle text data copied from a website
Target.Select
ActiveSheet.PasteSpecial Format:="Text", _
Link:=False, DisplayAsIcon:=False

Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0

'~~> Retain selection of the pasted data
Union(Target, Selection).Select

LetsContinue:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub

Any ideas where the problem is?

[snb]

You overlooked the simplest solution in #3

[danielw7]

You mean your solution #5 in the link? Which gives me the same error, meaning that I have a general knowledge problem...

[mancubus]

@snb

the users sometimes create miracles when filling in pre-formatted forms, tables, etc. they simply open a blank sheet, create their own table and directly paste it to formatted area.

the OP wants to disable all paste options the users can use and, it seems, chooses an undo operation.

[mancubus]

it worked for me...
can you upload a workbook with sample data and formatting.

[mancubus]

all the users must "enable" macros as well.

[danielw7]

I bet the code is working, but the way I use it is the problem. I created an empty workbook with several sheets for a data collection. To guide the user and to reduce the workload, the file includes a lot of conditional formattings. Now I want to force pasting values only with the code of Siddharth Rout above (or any other working code). I copied the code in a vba module in excel, but when I run it I get the error "Argument not optional". Did I forget to determine a variable in the code above?

[mancubus]

since it's a workbook "event" code, copy-paste the code to ThisWorkbook code module.

[danielw7]

Thanks. The code is not working for me though, since it gives a warning message every time I do anything (copy, write, paste), but it overwrites my formats anyway. Shouldn`t the code prevent my formats?

[mancubus]

it worked for me...
can you upload a workbook with sample data and formatting.

[snb]

I referred to the wrong post:

You overlooked the simplest solution in #3 ?

@macubus

If you use a userform and keep all the worksheet that matter very hidden, and if closing the userform will close the werkbook automatically too....
The last thing I would do is change the UI.

[danielw7]

please find a testfile attached. this is a very small part of a huge file for data collection, where user have to insert their data, some of them mandatory, some of them if needed. the mandatory cells have conditional formattings, which should not be overwritten.
thanks for all your efforts!

[mancubus]

if i was assigned this task when i had very little experience of excel and VBA, i would use a protected sheet, where data validation is the only way to enter data. (tx God, i have a bit more experience today:) )


@danielw7
snb's last suggestion sounds good. but i dont know if you ever tried to customize the ribbon.
we have a dedicated forum here:
http://www.vbaexpress.com/forum/forumdisplay.php?96-Office-2007-Ribbon-UI

[danielw7]

Thanks for having a look at the file. To get more knowledge though: do you know where the problem is in the file?

[mancubus]

i was writing my message after snb's post and didn't see your file.

[mancubus]

it also works for your file.

when i copy paste from an unformatted cell, the cell in column G keeps its formatting.

[snb]

I'd suggest to study the CF implementation first.

in cell Q4

The CF rule that will format the cell as yellow should read as:


=($A4<>"")*(G4="")

the use of IF(condition,1,0) in CF rules is redundant.

I'd prefer to make an inventory of CF rules.
When closing the worksheet (or at any other desired moment) the CF rules can be re-implemented.