VBA 2003 cut/copy | paste values only

[Guest]

How to allow cut/paste, copy/paste the cell values only; no formatting like
borders, colors, etc.

 

[Otto Moehrbach]

Use Range("B1").Copy to copy then use one of the following to paste:



Range("A1").PasteSpecial Paste:=xlValues

Range("A1").PasteSpecial xlPasteValues



HTH Otto

 

[Guest]

Otto,
Thank you for your quick reply. I'm sorry but I didn't sufficently
explain my situation.

I have a protected sheet which contains 4 ranges that are unlocked so that
the user can enter values. I would like to allow the user to select cells in
the unlocked ranges and use the main menu, or shortcut menu selections:
Edit|Cut or Edit|Copy and then Edit|Paste to cut or copy / paste within these
ranges. Unhappily, all the formatting goes along with the cut or copy to the
clipboard and then messes up my "pretty" formatting. For instance, one of the
unlocked ranges is bordered around with a thick border, if the user makes a
selection along the bordered edge and then pastes into the middle of the
range, the thick border comes along. Likewise, if the user selects cells and
then uses cut, the pretty green background color of my range becomes a gaping
white hole.

I hope this explains my request a little better and that you might have a
suggestion to help me.
Thank you,
Jeff Higgins

 

[Otto Moehrbach]

Jeff
I gave you a programming solution because your query is posted in the
programming newsgroup. I see now that your problem relates to manually
working with the worksheet.
Do this. Say you want to copy A1 to E1 and you don't want to disturb the
formatting of E1 with that of A1. Select A1 and do Edit - Copy or do a
right-click on A1 and select Copy from the shortcut menu.
Select E1 and do Edit - Paste Special and select "Values" from the
dialog box. Click OK. Does this do what you want? HTH Otto

 

[Guest]

Otto,
Again, thank you for your reply I appreciate it very much. Apparently I
have made a mess of the few posts I have made here. I suppose I didn't know
myself what I wanted to accomplish. Unhappily no one else here could read my
mind (or posts) and tell me what I wanted to do. I have come up with a
solution, I don't know if it the easiest or most appropriate, but it seems to
work. I've left out all of the copy/paste code here, but you can probably see
what I'm after.

Thanks again
Jeff Higgins

A class module named "EventClass" contains:

Public WithEvents App As Application
Public WithEvents CutMenuCommand As Office.CommandBarButton

Private Sub CutMenuCommand_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
CancelDefault = True
Call Sheet1_OnCut
End Sub

--------------------------------------------------------------------------------

A standard module named "WorksheetFunctions" contains:

Public AppClass As New EventClass
Public CmdBars As CommandBar

Public Sub Init_Workbook()
Set AppClass.App = Application
Set CmdBars = AppClass.App.CommandBars("Worksheet Menu Bar")
Set AppClass.CutMenuCommand = CmdBars.Controls("&Edit").Controls("Cu&t")
End Sub

Public Sheet1_OnCut()
MsgBox ("Cut menu_item Clicked")
End Sub

----------------------------------------------------------------------------

"ThisWorkbook" module contains:

Private Sub Workbook_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Workbook_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Workbook_Open()
Call Init_Workbook
End Sub

--------------------------------------------------------------------------------

"Sheet1" module contains:

Private Sub Worksheet_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Worksheet_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Disable Edit|Cut / Edit|Paste operation in the sheet named "Sheet1"
'In case I've missed any toolbar/menu options that will cause
Cut/Copy/Paste
If AppClass.App.CutCopyMode = xlCut Or AppClass.App.CutCopyMode = xlCopy
Then
AppClass.App.CutCopyMode = False
AppClass.App.CellDragAndDrop = False
End If
End Sub