Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Paste value only

Posted by David on September 22, 2000 12:28 AM
On a particular sheet I want the user to be able to paste value only. so if they hit control v it automatically pastes value instead of everthing. I also want it to work from the edit menu also if possible.

Check out our Excel VBA Resources

Re: Paste value only

Posted by Celia on September 23, 2000 8:06 PM


David
Since no-one has replied yet :-

Assign the shortcut Ctrl+v to the following macro :-

Sub PasteJustValue()
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

However, if the workbook that contains this macro is open, I think Ctrl+v will run the macro on whatever workbook or worksheet is active. If the workbook containing the macro is not open, Ctrl+v will do a normal paste.
There is probably some event-procedure way of restricting the shortcut to a particular sheet(or workbook) only, but what I do instead (because it's simple) is to have a toolbar button with the above macro assigned.

Regarding the Edit menu, there is probably an easier way, but all I can think of is to do a Workbook_SheetActivate macro that deletes (or de-activates) the Paste menu item and adds your own menu item (for paste value only). And then reverse the procedure in a Workbook_SheetDeactivate macro.
Don't know offhand the syntax involved to do this, but I'm sure you can work it out.
Celia


Re: Paste value only

Posted by Ivan Moala on September 26, 2000 4:40 AM

David as a follow on to my email and using Celias
suggestions then this should work...note the comments;

An interesting problem as there are a number of
things to consider......you'll have to change
the sheet name to the one you want to activate
the Disable Paste macro.
Also note that the Copy buttons (except for the
one in the commandbar) will be disabled.....the
Copy via Ctrl C however has not (sorry forgot) you
can add this to the code ??
The copy button on the commandbar diverts to a
SimCopy macro which uses the ClipBoard Directly
this is so that you do not get the CopyMode active
and therby anable the user to hit ENTER to paste.

If you require mods the post.....


In your Thisworkbook obj place this code;

Private Sub Workbook_Activate()
If ActiveSheet.Name = "Sheet1" Then
Call DisablePaste
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DisablePaste
End Sub

Private Sub Workbook_Open()
Call EnablePaste
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" Then
Call DisablePaste
Else
Call EnablePaste
End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Call EnablePaste
End Sub


In a Module place this code;

Option Explicit
Dim Ctrl, i
Dim Cpy As DataObject

Sub DisablePaste()

'Divert Copy command to your macro
'You need to divert away from copy STD due to the fact that
'the user can still copy and paste via COPY then press ENTER !!
Application.CommandBars("Edit").Controls.Item("Copy").OnAction = "SimCopy"
'Divert Paste to your macro to simulate a paste by Val
Application.CommandBars("Edit").Controls.Item("Paste").OnAction = "NoNo"
'Disable Paste Special & Paste as Hyperlink buttons
Application.CommandBars("Edit").Controls.Item("Paste Special...").Enabled = False
Application.CommandBars("Edit").Controls.Item("Paste as Hyperlink").Enabled = False

'Disable ALL Paste & Copy buttons on commandbars where ever they may be
For Each Ctrl In CommandBars
For Each i In Ctrl.Controls
If InStr(1, i.Caption, "Paste", 1) + InStr(1, i.Caption, "Copy", 1) <> 0 Then
With CommandBars(Ctrl.Name)
i.Enabled = False
End With
End If
Next
Next

'Disable right click on Sheet Cells which also gives you option to paste & Copy
Application.CommandBars("Cell").Enabled = False

'Divert "Ctrl V" KEY: Note small v NOT CAPITAL V
Application.OnKey "^{v}", "NoNo"


End Sub

Sub EnablePaste()

Application.CommandBars("Edit").Controls.Item("Copy").OnAction = ""

'Enable Paste, Paste Special & Paste as Hyperlink buttons
Application.CommandBars("Edit").Controls.Item("Paste").OnAction = ""
Application.CommandBars("Edit").Controls.Item("Paste Special...").Enabled = True
Application.CommandBars("Edit").Controls.Item("Paste as Hyperlink").Enabled = True

'Enable all Paste & Copy buttons on commandbars
For Each Ctrl In CommandBars
For Each i In Ctrl.Controls
If InStr(1, i.Caption, "Paste", 1) + InStr(1, i.Caption, "Copy", 1) <> 0 Then
With CommandBars(Ctrl.Name)
i.Enabled = True
End With
End If
Next
Next

'Enable right click on Sheet Cells which also gives you option to paste
Application.CommandBars("Cell").Enabled = True


'Divert "Ctrl V" KEY
Application.OnKey "^{v}", ""


End Sub

Sub NoNo()

MsgBox "Pasting Values only"

Cpy.GetFromClipboard
Selection = Cpy.GetText(1)

End Sub


Sub SimCopy()
Set Cpy = New DataObject
'Usiing the clipboard directly eliminates the problem you get
'when you copy STD ie. you are able to Paste via pressing ENTER !!
Cpy.SetText Selection
Cpy.PutInClipboard

End Sub

HTH

Ivan




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.