Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 2003 cut/copy | paste values only
How to allow cut/paste, copy/paste the cell values only; no formatting like
borders, colors, etc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 2003 cut/copy | paste values only
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 "Jeff Higgins" wrote in message ... How to allow cut/paste, copy/paste the cell values only; no formatting like borders, colors, etc. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 2003 cut/copy | paste values only
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" wrote: 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 "Jeff Higgins" wrote in message ... How to allow cut/paste, copy/paste the cell values only; no formatting like borders, colors, etc. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 2003 cut/copy | paste values only
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 "Jeff Higgins" wrote in message ... 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" wrote: 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 "Jeff Higgins" wrote in message ... How to allow cut/paste, copy/paste the cell values only; no formatting like borders, colors, etc. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA 2003 cut/copy | paste values only
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 "Otto Moehrbach" wrote: 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 "Jeff Higgins" wrote in message ... 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" wrote: 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 "Jeff Higgins" wrote in message ... How to allow cut/paste, copy/paste the cell values only; no formatting like borders, colors, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell 2003 copy and paste | Excel Discussion (Misc queries) | |||
Copy in Excel 2003 Paste in Word 2003 | Excel Discussion (Misc queries) | |||
Copy and paste problem Excel 2003 to Word 2003 | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |