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!!!
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 ....................................
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.
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.
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.
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?
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.
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.
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".
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.
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?
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.
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
Notes:
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:
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
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.
How to Disable Paste into Certain Cells (using VBA)
(ctrl + v, right click paste & paste special, menu paste/paste special)
I want every type of paste possible to be disabled for those cells listed below. (the cells cant be locked, its ok for the user to "TYPE" in the cell just not paste.)
it was possible to disable pasting in an Excel workbook. He didn't want Copy disabled, as he needed to paste data out of this tool into other workbooks, but just wanted Paste and Paste Special disabled while viewing this particular workbook.
Public Sub TogglePaste(booPaste As Boolean) Dim cmb As CommandBar, cbc As CommandBarControl, cbp As CommandBarPopup Dim cbpEdit As CommandBarPopup, cbcPaste As CommandBarControl Set cmb = Application.CommandBars(1) For Each cbp In cmb.Controls If cbp.ID = 30003 Then Set cbpEdit = cbp Next cbp For Each cbc In cbpEdit.Controls If cbc.ID = 22 Or cbc.ID = 755 Then Set cbcPaste = cbc cbcPaste.Enabled = booPaste End If............................
I need to be able to prevent any user from cutting and pasting data in one of my spreadsheets. I have protected it all, removed cut and paste from the options- edit tab, but you can still cut and paste with the right mouse shortcut key.
I have a workbook in which i do not want the end user to be able to drag and drop cells. I noticed that when I set this in the Excel options to disallow this, it is local to the machine. Is there a way I can prevent others from dragging and dropping cells, through vba or other means, without changing the users local machine settings? I also need to prevent CUT and pasting of cells. I also need to leave the cells un-protected, becuase COPY and paste is ok, as it doesnt affect the formulas that refer to the cell. The worksheet and workbook are both protected.
I have workbook having 3 sheets out of them 1 sheet name "report". I want vba to disable copy /past option to sheet Report only with msg " copy not allowed" if key clt+c or copy option selected
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.
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?
