Macro to only allow pasting of values and not format??

[Celt]

Hi Carim,

I guess I don't know where that email goes. Can you send it to thi
email:

(e-mail address removed)?

Take out the nospam of course!

Thanks

 

[ben]

Hello,

I have the same needs than you :

I need to protect the automatic format of Excel cells but to let users
to input values.

I don't know what is the best method. I also thought to have some
"reference" cells in a hidden sheet and to copy the format when cells
are modified but I'm using events for other purposes and I didn't find
the good way to do so.

From now I've protected from Ctrl+V commands in using events and OnKey
Method to trap the CtrlV.

1) Create "Class" with :
Public WithEvents App As Application

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

2)In "ThisWorkbook" :
Private Sub Workbook_Open()
Application.OnKey "^v", "MyCtrlV"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "^v", "MyCtrlV"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^v"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^v"
End Sub

3) In a module :
Sub MyCtrlV()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

I'm now thinking to modify the menus of Excel in my application (Edit
menu and Right key click), so the Paste command will have the same
behaviour as "past special value". I'm not expert in VB but I think that
should be possible and will complete the protection.

Hope it can help you also,
Benoit

 

[Celt]

Thanks very much Benoit!!!

That really does help.

If you are able to get the coding to work on the other menus, I woul
love to see how you did it.

One other thing I was thinking... you would also need to alter th
return key as users can paste using that as well, right?

Good luck!
Celt

 

[ben]

Yes Celt,

Even if I'm using excel for years I sometime learn so basic things !...

I've done what you said but also renamed "MyCtrlV" in "MyPaste" and
changed the macro :

Sub MyPaste()
' Will act like Copy paste Value
If Application.CutCopyMode Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub

For "ThisWorkbook" it is now including the following code :

Private Sub Workbook_Open()
Application.OnKey "^v", "MyPaste"
Application.OnKey "{RETURN}", "MyPaste"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "^v", "MyPaste"
Application.OnKey "{RETURN}", "MyPaste"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "^v"
Application.OnKey "{RETURN}"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^v"
Application.OnKey "{RETURN}"
End Sub

For the menus I don't know when I'll have some time to work on it. If
someone already have the solution on hand...

Benoit