# Disabling "paste" in EXCEL



## CastleHeart (May 4, 2002)

I have an xls that requires data be copied from an HTML site. It must be pasted as text or it will blow my xls's mind or at least sit forever trying to copy the info. I therefore have a *COPY* button that does this for them. But sometimes folks get flying along and by habit just do a *CTRL V* and then cuss a lot when it hangs.

I used code to disable paste in the edit menu - but what would be the code to gray out/disable the PASTE button and disable CRTL V ?

I would have it do so ON OPEN and then restore ON CLOSE. Works with what I've got but I really need CTRL V code to cover that possibility. If not I suppose I could always lock the cells and then have the PASTE button unlock, paste, and then lock again.

- Castleheart


----------



## jimr381 (Jul 20, 2007)

I am just throwing out an idea, but have you tried mapping CTRL-V to another command from the key mappings?


----------



## Anne Troy (Feb 14, 1999)

My first thought, too, Jim.


----------



## CastleHeart (May 4, 2002)

Well here's what I ended up doing:

I used the code:
Private Sub Workbook_Open()
Application.CommandBars("Standard").Controls("Paste").Enabled = False 
Application.CommandBars("Edit").Enabled = False 
to disable the PASTE button and gray out the EDIT menu.(for those tempted to paste from there!) 
This eliminated the chance of us pasting normally.

I then created a macro: 
Sub CTRLV ()
ActiveSheet.PasteSpecial Format:="Text"
End Sub
with the shortcut of ... you guessed it .... *CTRL V *!

That shortcut then supercedes the standard CRTL V (within this spreadsheet) and pastes the HTML data as text. 
I did have one troublesome scenario that could arise and so I added a macro with the shortcut CTRL C to pop up a message box that reminds them(us) what to copy and what not too and thereby prevents them(us) from using CTRL C to copy when we ought not!

I use a
Private Sub Workbook_BeforeClose(Cancel As Boolean)
with the appropriate code above to reactivate the controls that were temporarily disabled when closing the spreadsheet.

And it works like a champ !!! :up:

I could have just put a warning message on CTRL V but this way the whole thing flows smoothly and lets everyone paste away !!!

- Castleheart


----------



## slurpee55 (Oct 20, 2004)

Darn itC, that makes too much sense...wouldn't it have been easier to have Ctrl-V make a pop-up message appear about "You are about to format your hard drive. Continue?" or something?


----------



## jimr381 (Jul 20, 2007)

I would have thought of it going to a command prompt and typing deltree *.*


----------



## CastleHeart (May 4, 2002)

> I will now Format your hard drive... and no one will hear your screams....


S55, 

At last we see the true evil that lurks behind the man with the binary face! ! !

Still... I suppose that is better than having the code shutdown every menu bar, tool bar, scroll bar, hot bar and salad bar on their page and then ask them to say the secret word to get them back! 

Perhaps that is the perfect BEFORE CLOSE message for a future spreadsheet. I will of course list slurpee55 as the author in the credits!

- C


----------



## slurpee55 (Oct 20, 2004)

LOL - you -C-rack me up!
I wasn't like this before I came here....


----------



## CastleHeart (May 4, 2002)

Why didn't someone *TELL ME *that you could still right-click and paste from its menu !!!
Just goes to show you....._it's ALWAYS something !_

well .....ALRIGHTY THEN !

I added the following to put an end to all that:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh _
As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = False

If Sh.Name = "particularworksheetname" Then Cancel = True
If Sh.Name = "particularworksheetname" Then MsgBox "* SORRY HAL..... I can't let you do that !*"
End Sub

The message box was just sort of .... a little _extra touch_!

- Castleheart

Slurpee: feel free to use: MsgBox, 

" RIGHT-CLICK again and I'll Format your Hardrive !
Now... I know what you're thinking... will he do it or not. 
Well you've got to ask yourself one question - do I feel lucky? 
Well *DO YA, PUNK *! "


----------



## slurpee55 (Oct 20, 2004)

Oh, wonderful!!!!!!!!!!!!!
And April Fools day is coming up.....


----------



## slurpee55 (Oct 20, 2004)

CastleHeart, what about pasting from the Clipboard of the Task Pane?


----------

