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


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

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 View Related

How To Drag The Formula Down

May 19, 2014

On the 'Total' sheet I'm wanting subsequent sheets to fill in on the rows. Do I have to redo the formulas for each row because it will correlate to a different sheet? I only know how to drag the formula down, but that doesn't seem to work right!

View 11 Replies View Related

Avoiding VALUE When Drag Downwards

Jul 9, 2014

I have a perfect formula


It does exactly what I need , it does not calculate on empty rows , but it calculates on heading rows , how can I modify to avoid this.

See file attached , I don't want the value on b6 when I drag downwards.

07 09 value avoiding.xls‎

View 11 Replies View Related

Drag Down Formulas In VBA

Oct 22, 2012

I have a workbook with five different sheets in it.The first is an inputs sheet where I populate inputs to my file. The rest all contain formulas in a top row (ex: A2:G2 in the first sheet D4:M4 in the second sheet...etc). These formulas are calculating values based off my input sheet and need to be dragged down the same number of rows as is in the inputs sheet (if there are 125 rows in my input sheet I need it to drag down the formulas 125 times, so from A2:G2 to A126:G126 or D4:M4 to D129.

The main problem is that I never know how many rows will be on the input sheet so the piece of code would ideally count and then apply that count to all of the other tabs and drag down the appropriate amount of rows. It is a base file so I save a copy each time I open so the start rows will always be the same.

View 2 Replies View Related

Drag Down Only Change Columns?

Jun 23, 2014

I have a formula that I would like the column references to change as I drag the formula down. I would also like the row reference to stay the same.

I have attached my workbook for reference. I listed the value in red I would like to achieve


View 2 Replies View Related

How To Drag Formula Of Every Other Cell

Mar 14, 2014

=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A1:C2)

If I simply drag the above formula down I end up with

=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A2:C3)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A4:C5)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)

I am trying to drag the above formula so that it copies in the following manner:

=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A3:C4)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A5:C6)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A7:C8)
=DCOUNT(timeg15w!$A$1:$F$3909, "WPC",Criteria!A9:C10)

How can I achieve this result?

View 3 Replies View Related

Simulate Drag Or Autofill

Jan 3, 2009

i need a macro that will simulate the little corner crosshair that you drag to fill in and increment formulas. i need to fill down on sheet 2, columns a thru m a number of rows equal to sheet 1,column b, count -1.

View 9 Replies View Related

Drag The Formulas In Columns

Apr 11, 2009

I have data from F1 to AG1...I am unable to create a formulae & drag in excel in a column like

=H1.....If i drag after H1...it is reflecting as

View 3 Replies View Related

Drag Formula But Skip Every Other Row

May 11, 2009

I am trying to link the cells up in a new workbook the problem is the data I need is setup in a way the formula needs to skip every other row.
For example:

View 2 Replies View Related

Drag Down Top Row Of Formulas For Non-Continuous

Mar 19, 2014

Is there a way to select cells with formulas for a top row and then fill down the formulas for these non adjacent, non-contiguous, etc. columns? I can paste a single formula, but not varying.

i.e. Cells A1, D1, and G1 have my formulas, and I want to identify that they have formulas in the first place and then drag down to let's say A10, D10, and G10. I will not know which columns contains cells before hand, and the given formulas are not fixed

View 9 Replies View Related

Drag A Macro Across Cells

Jun 24, 2009

is there any way where instead of selecting a cell one by one and run a macro, i can just drag the cell with the macro across other cells like how a formula runs?

View 5 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

Vlookup And Drag Down Cell

Feb 18, 2010

I would like cell D2 (on Sheet1) to default to the value of "EACH", unless the value is found on Sheet2 (column C). If it is found on Sheet2, then I want it to match the unit beside the respective upc code (see Col Q on Sheet1, and Col A on Sheet2) . Then I would like to be able to drag down cell D2 and all cells on Sheet2 would match and any that do not match, would be filled with "Each". Desired results are shown in attached example file.

View 5 Replies View Related

Drag Formulas With Gaps?

Mar 13, 2014

I'm wanting to do is drag a formula down and it drop to the next cell rather than the same row number I'm on. For example I'm trying to concatenate a list of phrases whilst changing the main word. Here's an example of the excel sheet

Base Terms

There is meant to be a space after the second red and blue enabling me to make (in order), red car, blue car, car red, car blue

How can I make it so I've done the relevant concatenate formulas for A2 with the B column and simply drag it down and Excel will switch from A2 to A3 and so on when I've dragged out the 4 formulas?

View 5 Replies View Related

Drag Copy Formula Down

Jan 23, 2007

I have tried absolute and relative on the formula in A5 of this sample data below to get A6 to equal D2 and so forth down the sheet. Nothing works except typing in the formula for each cell....

View 9 Replies View Related

Shortcut To Drag Down A Formula

Apr 23, 2007

i have a formula that needs to be dragged down 65,000 rows in excel is there a quicker way of do it than with the mouse?

View 3 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

Formula Drag Over Grouped Rows

Dec 16, 2013

I have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:

and so on.

What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).

View 4 Replies View Related

How To Drag Down Numerical Value But Repeat 2 Rows

Jul 10, 2014

I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between and simultaneously keep the numbering in sequence only after the repeats 2 rows in between . So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.

At Present:
X4 1
X5 2
X6 3
X7 4
X8 5
X9 6
X10 7

I need to have

X4 1
X5 1
X6 1
X7 2
X8 2
X9 2
X10 3
X11 3
X12 3

and so on

I have a 2000+ rows to manually input and hence it is not practical!.

View 12 Replies View Related

Range Stays The Same When I Drag The Formula!!

Jul 29, 2008

I want the range to stay as i put it without it +1 when i drag the formula down my spreadsheet, below is the formula im using (for a creche in case your wondering)

=IF(G5<A8,"Baby",IF(G5<A16,"Wobbler", IF(G5<A24,"Toddler",IF(G5<A30,"Playschool","Please use DD/MM/YYYY FORMAT"))))

G5, A16, is taken from a list of dates down the side so i need these to remain constant otherwise my formula goes out.

View 3 Replies View Related

Drag Vertically And Copy Cells Across?

Sep 5, 2008

I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.

For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.

Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.

View 3 Replies View Related

Click And Drag A Cell Or Row Of Data

Sep 22, 2008

I understand how to click and drag a cell or row of data but is it possible to click and drag a group of rows of data? I have 12 rows of data that will repeat itself every 12 weeks. I can "save & paste" easily enough, but just curious if there is a way to click and drag 12 rows of data so that they remain the same for every group of 12 weeks.

View 2 Replies View Related

Drag & Fill Series Only In One Particular Sheet

Oct 18, 2008

I am not able to drag & fill series only in one particular sheet, in other sheets this function is working fine.

View 2 Replies View Related

Drag A Cell Without Adding To The Contents

Dec 31, 2008

I have an inventory sheet where I occassionally have to create more rows with the same information. So I will create rows and then copy and paste info into them. What I would like to do is be able to drag the information by clicking on the lower left corner and populate the cells that way. The problem is that if there is a number in the cell then it will automatically add to it. For instance, if I try to drag the contents of cell labeled klaraid 1112 then it will add incrementally to this in the cells below.....1113, 1114 and so on. If there are no numbers in the thing i'm dragging then it works fine. How do I drag cells with numbers without it adding to the contents incrementally?

View 3 Replies View Related

Drag Sequence Having Sheet Reference?

Oct 4, 2013

I would like this formula to increase down the column as follows:


Sequence is addidng 4 each time... Dragging results with:


I have seen the following work for others:

=INDEX(A:A, (ROW()*2)-1)

Can a macro be created to store the number as a variable and loop a certain amount of times adding four to that variable every time?

View 3 Replies View Related

How To Copy And Drag Down Formula In A Check Box

Nov 9, 2013

i wanna to put check boxes in colums d4:d41 with a formula inside which beginning with this formula (=Sheet1!$D$4) in cell d4.how to copy and drag down this formula into d5:d41?

View 8 Replies View Related

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