Disable Paste Function (Including Drag And Drop) On Excel Worksheet

Jun 27, 2014

How can I disable the paste function on an Excel worksheet, including drag and drop?

Preferably I still want to retain the ability to copy.

View 1 Replies


Code To Disable Cut And Paste / Drag And Drop

Dec 5, 2013

Which code is "better" to disable D&D, C&P....

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_Deactivate()

[Code] .......

OR This one:

Option Explicit
Private Sub Workbook_Activate()
With Application
.CutCopyMode = False
.CellDragAndDrop = False

[Code] ........

I noltice that one uses "Option Explicit" and one doesn't, one seems to be longer than the other, but when I tested both, they both seem to do the same thing. I did notice that neither one of them prevents a "paste" into the worksheet when something is copied from an outside source, but that is not a concern right now!!!

View 3 Replies View Related

Disable & Re-Enable Fill Handle & Drag-and-Drop

Jun 19, 2008

I've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.

The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?

'*** In a standard module ***
Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False ....................................

View 9 Replies View Related

Excel 2010 :: Populate Cells From Listbox Drag / Drop - Can't Find First Empty Row On Worksheet

Feb 1, 2014

I am running Excel 2010 and Windows 7.

I have a form with a main list box and several other list boxes. I drag and drop records from the main list box to the others. When I drop a record onto one of the list boxes, a corresponding worksheet is updated with the same record. This works just fine.

The problem I have is:I close and re-open the form after having added some dataDrag/Drop a record to the list boxThe worksheet won't find the first empty rowExample: If the worksheet already contains 4 rows of data, I have to drag/drop 5 times before the fifth row will be updatedI would like to drop the data the first time and have the first blank row updated


1.) I have tried variations of "xlUp", "xlDown", and SpecialCells(xlCellTypeLastCell) in the "Worksheets("Monday")... line of code; all to no avail.

2.) The cells contain formatting (borders)

Here is the relevant code:


'Copy items from the list box to the worksheet
For intI = 1 To ListBox2.ListCount
For intJ = 1 To ListBox2.ColumnCount
If IsEmpty(Worksheets("Monday").Cells(intI + 1, intJ)) Then
Worksheets("Monday").Cells(intI + 1, intJ).Value = ListBox2.List(intI - 1, intJ - 1)
End If
Next intJ
Next intI

View 2 Replies View Related

Copy And Paste Entire Worksheet Including Form Controls?

Dec 4, 2013

I need to make several exact duplicates of an existing worksheet which includes several form controls. Basic Copy/ Paste the worksheet works well but row heights are different and hidden rows are unhidden. Is there a way to make an EXACT duplicate ?

View 2 Replies View Related

Disable Copy/paste Within Worksheet

May 22, 2007

Is it possible to disable the copy/paste functions on a particular worksheet within a workbook, or failign that, on a complete workbook?

View 9 Replies View Related

Disable Cut And Drag Only Using VBA?

Jan 5, 2013

I need to disable cut and drag only in a workbook. The idea is to avoid formula reference errors when unlocked cells are cut and pasted or dragged. I can find many macros online but none that do only these two things.

View 2 Replies View Related

Disable Cut / Copy / Paste And Delete In Worksheet

Oct 28, 2011

I would like to disable cut/copy/paste and maybe delete inside a worksheet. Reading earlier threads on this subject provided the code to put in the ThisWorkbook module, where it works great for all my worksheets. However, there is one worksheet where it is necessary to have copy/paste, either with control c or right click/copy. Here is part of that code provided by Tom Urtis:

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

What do I replace Application with to work on just the worksheet level?

View 2 Replies View Related

Excel 2010 :: Worksheet Move / Copy Function And Paste Special / Columns Widths

Mar 18, 2014

I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook

The steps I'm using are:

1. Right click the tab name
2. Select Move or Copy,
3. Select Create a Copy
4. Click OK

Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.

I've also used Copy, Paste Special and selected column widths and that does not work either.

View 3 Replies View Related

Disable Copy / Paste Into Excel Workbook?

Feb 16, 2014

I Have a spreadsheet in which data is spreading into column A-Z. I would like to disable copy, paste, ^V,^R,^C, ^D for certain columns (Q-U) of the workbook so that user is forced to enter data manually on these columns.

Is there any simple of doing this using VBA.

View 2 Replies View Related

Stop Drag And Drop Across A Row

Jan 14, 2010

How can I stop drag and drop across a row but you can drag and drop down a column.

View 14 Replies View Related

Drag & Drop Between Workbooks

Jun 3, 2008

When I drag a text file from my desktop into an existing Excel worksheet it automatically creates a new workbook. I'd like to know how to modify this such that I can drop the file into my existing worksheet (and not have a new workbook create) and then run some VBA macros on it.

View 3 Replies View Related

Drag And Drop Of Non Sequential Cells?

Jun 20, 2014

Drag & Drop Example.xlsx

In the example attachment on the weekly tab, you will see I have daily totals and a sum for weekly. On the summary tab I have the columns going down with a link to the weekly totals.

I have over 5 years of data in this format and don't want to link each cell in one sheet to another. If I drag and drop, obviously the weekly doesn't come down correctly.

Is there another way as I don't want to have to do this for over 250 weekly totals (and counting) manually.

View 3 Replies View Related

Listbox Multiselect Drag & Drop

Aug 18, 2007

I've finally gotten the code down to drag an item from one list box and drop it into another listbox, but only for one item at a time. The list boxes are on a user form and I am using Excel 2002.

how to select multiple items in ListBox1, drag them to and drop them into ListBox2?

View 9 Replies View Related

Drag-and-drop To Trade Cells

Apr 29, 2006

I have a spreadsheet where I often have to exchange pairs of cells. I currently do this by dragging Cell A to a blank portion of the sheet, dragging Cell B to Cell A's former location, then dragging Cell A to Cell B's former location. I was just thinking that I might be able to use VBA to allow me to drag Cell A to Cell B and have them "trade places".

I don't even know what events are involved in the dragging process so I don't know where to start.

View 7 Replies View Related

Allow Drag & Drop For UserForm Image

Nov 1, 2006

I am looking to if there is anyway to move an image box using a mouse drag and drop action. I have put together a little example of what I am trying to work with and was wondering if anyone on here had any tricks to make this work.

I realise the drag and drop method only applies to the textbox and listbox but have seen some old code that can simulate the drag and drop by creating a border of some sort to illustrate where the image box is going to be placed and final just setting the properties of that image box to where the border is placed.

In the example type in a size in the width and height textboxs (I used 3000,3000) and then what I want to do from there is move the white box using the mouse within the blue box.

View 3 Replies View Related

Drag Drop Treeview Node

Jan 10, 2007

I was trying to drag a node onto a worksheet and for that node to remain in the treeview control.

I have found out that if I hold the CTRL key then the node remains on the treeview.

Does anyone know how I can implement this action in my code so I don't have to keep pressing the control key?

View 9 Replies View Related

Modified Drag And Drop Via Left Click

Mar 23, 2013

I am wondering if it is possible to drag & drop, via left-click, the data in a cell but WITHOUT affecting it's reference -- i.e., like "Copy here as values only".

View 1 Replies View Related

Override Cell Drag & Drop Behavior

Oct 13, 2007

For example. Dragging c7 to e5.

My range before dragging and dropping...

After the drop.

What do you think? I think it can be done but do not want to waste time going about it the wrong way. Please give me any ideas on how you would approach this problem.

Some information to start with. The modal dialog that pops up can be dealt with.

This one.

I can determine if I want to insert down or up. I am not concerned with shifting left or right at this point.

I can also determine the original address and destination address of the drag and drop operation by using the change event.

The ranges being dragged will be limited to a single cell.

View 9 Replies View Related

App To Draw Curved Shape To Scale With Drag & Drop Capabilities

Dec 13, 2006

I am researching the possibility of using Excel 2003 WITHOUT a Cad or Drawing package to simulate a drawing. (Consultants inquiries are WELCOME). The idea is to prompt the user (Sales Rep or Customer) for a few key dimensions and then plot out a closed curve based on the coordinantes entered. This shape, which would need to be accurately scaled, would then be sectioned and used as a canvas for dragging and dropping various icons.

Why use Excel instead of CAD?

Other than "everyone" having it, the placement of the "icons" relative to the perimeter of the curve will be used to estimate material costs and generate a quotation like a configurator application.

Key hurdles seem to be the drawing of a curve with cells as pixels and using drag & drop with custom icons - possible as an Add-In?

View 9 Replies View Related

Static Formula Reference: Delete And Drag-and-drop Data To Get Rid Of Any Bogus Values

Jan 10, 2008

I making a template for multiple users to import data into for analysis. I want them to be able to delete and drag-and-drop data to get rid of any bogus values. However i need forumlas that reference those to NOT follow the values as there as moved. As a simple example say you have two columns, column A is a set of random numbers, column B is the number from coumn A times 2. If take the bottom half of column A and drag it up one cell, overwriting a cell, now the cell in column B next to the overwritten cell has a #REF err and ever cell below that now references a Cell in column A from the previous row. I want a formula that references a static address and will not follow the cell if it is moved.

If I use named ranges rather than specific cells references it works for the above example, however for my actual form it does not. Even though the ranges and formulas stay the same I start getting incorrect values. If there is no other way to this I will try to figure why the named ranges do not work.

View 3 Replies View Related

Tab Names In Formulas...drag And Paste?

Jan 23, 2009

I have a workbook which contains 12 sheets, 1 for each month of the year. I also have over 100 sheets, 1 for each agent that covers their stats for each month of the year based on some of the cells from the monthly sheets.

I am trying to drag the formulas over but it will always just refer to January, instead of January and then next cell refers to the February tab, etc, etc.

View 9 Replies View Related

VBA To Copy / Paste (including Formulas) To Next Available Row

Mar 20, 2014

I need some VBA to copy row 2 then paste it (including formulas) into the next available row in the same sheet but then also clear the data in row 2 but retain the formulas.

View 2 Replies View Related

Average Including Zero After Paste Link Transpose?

Feb 15, 2012

Average formula that will give an accurate answer. My issue is that I need an average of what has been entered rather than for the entire selection. I have paste link transposed the data as it needs to run on Excel 2003 which can only hold 30 items in the Average(number1, number 2 ... etc) and I need 52. My problem occurs when the data is paste linked all blank cells appear as a Zero therefore increasing the count of the average and providing a wrong answer.

Eg when (1, 2, 0, 3) Average = 1.5 is entered the formula works out (1, 2, 0, 3, 0, 0, 0, 0, 0, 0, etc) Average = 0.12 because it divides by 52 instead of 4. As you can see I can't ignore zeros as they are a possible input. What would be perfect is for the paste link to paste the blank cells instead of the providing a zero then the plan =Average(B3:B55) would be fine.

(Note: the new entered data is on the same row but different columns hence the reason for the paste link transpose to get all the data in one column)

View 2 Replies View Related

Disabling The Control Drag And Fill Function

Mar 29, 2007

the function I'm referring to is when you have a value in a cell and you hover your mouse cursor over the edge and get the black cross with arrows on all corners. Once you drag the cell into another location, it copies the the data into the other cell.

I need a way to stop this from happening, because this causes reference errors on other sheets when users do this by accident. However, I still need to allow the user to be able to type in that cell, use the autofill option, and the Ctrl+C option. Is there a way to program a macro that can be put in the sheets to do this for specified cells or ranges?

View 5 Replies View Related

Copy And Paste Values Without Including Dropdown List

Jul 15, 2014

The source data is collected using drop-down menus. In moving the data from one workbook to another, the drop-down lists are carried over into the destination cells. I have used both: paste special "values" and paste special "valuesandnumber formats". The code in the else statement produces the same result as the 2 step process above it.

View 1 Replies View Related

VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1

Jan 8, 2009

I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

View 2 Replies View Related

Excel 2010 :: Copy And Paste Function?

Apr 18, 2012

The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.

When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.

As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.

I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.

View 2 Replies View Related

Including Worksheet Tab In Formula

Jul 8, 2013

I have a workbook with numerous worksheets. Each worksheet is similar to all the others, and I want to be able to (for example) lookup the values in cell C10 for each worksheet and record the value in a summary worksheet.

The name of each worksheet is in Row 1. Is there a way to reference the sheet name using the values in row 1?

View 3 Replies View Related

Excel 2007 :: Insert Paste Function Into A Cell?

Nov 25, 2011

I'd like to know if it's possible (Excel 2007) to insert a function/button into a cell, which when clicked, will paste whatever is on the clipboard?

You can see a screenshot of my document here, and I'd like to insert a paste funtion in the red circle. Is it possible?


View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved