Create a message box when someone uses the drag/drop feature

To start, I am VBA illiterate, so whatever your response, please dumb it down for a 3 year old. Thanks. Make that a 2 year old.

Our office uses an Excel spreadsheet for travel approval. There are tabs for each month so 12 tabs (plus hidden tabs for my admin use)

I have conditional formatting and data validation in each tab. The problem is when travelers copy/paste and drag/drop - it screws up my conditional formatting (and data validation), plus we've had issues with people copying previous travel and not changing the travelers names so we don't know who is actually going on the trip.

I created a message box to show when the spreadsheet it opened that tells people NOT to do any of that, but of course they ignore it.

I've found a code to prevent the copy/paste, so that hurdle is done (I do have a question about that too though). But I've searched online and have found nothing to prevent the drag and drop except to disable it for the entire Excel program. I don't want that. If there is a way to prevent it for only this ONE workbook, that would be great.

However, since I don't think that is possible, I am trying to create a message box that whenever someone DOES execute the drag/drop, and message box appears telling the user this feature isn't allowed.

Ideally a code could be written to then undo whatever the user did by drag/drop.

I hope all that made sense. Again, there are tabs for each month, so whatever the solution, it needs to be applied to them all.

Thank in advance!!

Oh, and about the solution I found to prevent copy/paste.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

This code works great. But I would like to be able to toggle this on and off easily. Is there a way to add key strokes so I could do something like Ctrl + Alt + D (or whatever)? Just a set of key strokes that would turn the above code on and off (because I hate the idea of adding and deleting the code whenever I need to copy and paste).

Thank again.

Hi gmr4evr1,

Thanks for responding. Unfortunately, the code did not work. I created a Module1 in ThisWorkbook, and pasted your code.

I am still able to use the drag and drop, and the copy/paste works too. The shortcut keys don't seem to do anything either (I am not worried about that as much though).

Originally Posted by cytop

If i may, it might be a better idea to put the CellDragAndDrop lines in the Workbook Activate and Deactivate events, then drag and drop will be enabled if the user switches to another open workbook.

On second reading perhaps all the code should be moved...

Hi cytop, how would I do this to try?

I created a Module1 in ThisWorkbook

The code goes in the ThisWorkbook code module, not a standard code module... You'll find the Activate and Deactivate events using the 2 dropdowns at the top of the code window. Select 'Workbook' in the left hand drop down and the various events will be available from the right hand dropdown.

Thank you both! The drag and drop has been disabled for what looks like just this workbook. I opened a different spreadsheet and drag/drop worked for that one. Yay!

But the copy/paste still worked.

When I added this code below to the specific tab (module?) it works. I am going to see if I can add a shortcut key to that code which works.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Thanks again! I'll keep you posted.

Originally Posted by gmr4evr1

Sorry about that, I hadn't tested the CutCopy part of the code, but I'm glad you were able to get it to work.

I wasn't able to figure out how to add a shortcut key, but thank you again for getting me so much closer to what I need.

Instead of a shortcut key, could a button be created to trigger the copy/paste on and off? I don't want the other users to see the button, so I could put it on one of my hidden tabs just for me.

Any suggestions would be much appreciated!

Thanks!
(I am leaving work shortly so I will pick this back up in the morning. You have lots of time to ponder.) :-D

Originally Posted by Winon

@gmr4evr1,

That is strange?

Please try this one.

Regards.

Hi Winon,

Thanks!! I see the term "toggle" in the code. As I said, I am VBA illiterate, so does that mean you've included a way for me to "toggle" these features on and off? I didn't see anything in the code to show me what that could be, so I am guessing "toggle" means something else.

And why does the formula bar need to be hidden? I hate the fact that I don't speak VBA. I have tried, and tried to no avail to learn the language.

Thanks again.

Originally Posted by Winon

Hi MKE2014,

Thank you for the feedback.

Would you like Buttons to "toggle" these features on and off?



That is to sidestep one of the flaws in Excel, where you cannot prevent Copy from the Formula Bar and paste to another Cell. The average Excel user is unaware that they could Copy and Paste that way, and I have not yet encountered an incident where someone was able to figure that one out!

Regards.

Yes, a button to toggle these feature on and off would be soooooooo awesome!! Thanks!!! I have hidden tabs and I can hide the button there.

Originally Posted by Winon

O.K. MKE2014,

Here you go. Enjoy!

Regards.

Hi Winon, I wasn't able to open the file.

Thank you!! Sorry for delay; work has been soooo crazy this week.

When I have the "Copy Off" selected and try to paste something, I get an error: "Run-time error '1004;" Method 'Undo' of object '_Application' failed"
I click on debug and it highlights Application.Undo in the code, no matter which sheet I am using.

Also, why does sheet 1 have the #1 and Hello; sheet 2 have the #2 and Cheers and Sheet 3 have the # and Bye written on them? Were those tests?

Thanks again!

I can't open the new workbook. I get the same error message as the one you posted on 3-16. I'll see if I can attached a screen shot.

screenshot.jpg

I hope this image comes through ok.

Nope, that one didn't work either. Are you able to tell me how you fixed the code in the file I was able to download? I really, really appreciate your help. I'm sorry the file is being difficult.