Force Paste Special Values

[hatman]

HAH... I think i have asked more questions today than in all the time since I became a member... I guess that means I'm pushing the envelope.

Anywho. I have a workbook where I want to retain FORMATS in all of my cells in all of my sheets, but allow the user to enter data. So I got this code to undo PASTE and instead do a PASTE SPECIAL VALUES:

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

    Dim UndoString As String

    On Error GoTo err_handler

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
            Application.ScreenUpdating = False
            Application.Undo
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.ScreenUpdating = True
    End If

    Exit Sub

err_handler:

End Sub

Only I had a co-worker submarine this code by simply entering some data, then dragging to fill across. All my formats went south. Any way to fix that?

 

[Bagel_Mon]

Just wondering if I'm doing something incorrect.. I'm very inexperienced with VBA.

We simply copy the code and paste it in the 'ThisWorkbook' file within the Microsoft Excel Objects folder..? Besides saving this & the workbook as a macro-enabled Excel, what am I missing?

Thanks in advance!

Nevermind! It was an issue with my formatting.. Thanks for the code!

 

[nikomonese]

Nevermind! It was an issue with my formatting.. Thanks for the code!

Hi everyone, I can't make it work for me. I'm doing the exactly same things that describe @Bagel_Mon. Is it actually working with the ctrl+c and ctrl+v shortucts? Sorry if my english is not properly but I'm an Argentinian. Regards, Niko.

 

[nikomonese]

Well, I disabled the OnError step, so I could found the error.
For discards some incompatibilitys with my actual project I made a new .xlsm workbook and paste the shared code in the ThisWorbook objet.

 

[nikomonese]

Sorry for the multiples posts, may could be a Language issue? I'm using the Spanisn version.

 

[nikomonese]

CONFIRMED, it was a excel language issue! I changed it to english and it works properly.
Is there a way to make the code multi-languages?
Sorry for the multiples posts, at least these may help to someone like me...

 

[nikomonese]

Is there a way to read, accord to the language selected, the "Auto Fill" , "Paste" and "Undo" strings? because if that is possible, may be the way to make it universal.
Please I will aprecciate if you tell me that, thanks for the code.
If it is not possible, may be I search what languages codes are for english and spanish and entApplication.CommandBars("Standard").Controlser a conditional at beggining with the corresponds translations.

YES IT IS THERE A WAY.

[/B]
    Undo = Application.CommandBars.FindControl(ID:=128).Caption
    
    Paste = Right(Application.CommandBars.FindControl(ID:=6002).Caption, _
                    Len(Application.CommandBars.FindControl(ID:=6002).Caption) - 1)
                
    AutoFill = Application.CommandBars.FindControl(ID:=24836).Caption
[B]

Thanks for all, Niko.