Excel? URGENT!

[Dmack]

Can you using or not using VB scripting cause people to not be able to cut and paste cells throughout a workbook? We are having serious problems with locked and password protected files suddenly having "#REF" all over the place due to someone cutting and pasting data in... Is there a command or process I can use to kill this Problem???

[Steph]

Bonjour in Ottawa !

One quick way would be to protect your worksheet (cells), that would prevent people from pasting stuff where they shouldn't... but then again I don't think this solves your problem ?

[Dmack]

Nope it does not solve the problem... Nice try though... That was what I did to solve the other problems... I even wrote a VB script that locks or unlock specific areas depending on who is using the workbook... But they cut and paste data to another area and My Trends sheets go all "#REF" crazy on me... I thought that coding the equation to check certain cells no matter what would help and all that happens is where they pasted turns it's Reference cells to "#REF" instead of where it was cut from and where it was pasted to... Is there a way to add a command to the spreadsheet that

OnRight Click
Give MsgBox "HEY, Don't do that!"
close

So that they don't get the menu?

and then set it do they can not get Cut or copy from they Edit menu????

But, Only in that set of files not all files...?

[Dmack]

PLEASE ANYONE!

[Steph]

You could create an Add-in that would start automaticly with only the sheets you want, that would popup your own menu on the right click of the mouse.

Steph

[DJDANNYK]

Dmack,

Are the cells that you are trying to protect formulated cells or do they contain sensative information...??

[andymannewbie]

maybe you could do this:
Add in a VBscript that whenever it sees the line of text #REF then it will MSgBox the user and then delete the text ? I am not familiar with excel, so i don't know if you cna do this or not, but it would be something with len in the VB code....

[Dmack]

The cells that are protected are formulas... They are locked... Problem is that when a user cuts then pastes data we end up with missing references... Hence "#REF"... Guess it can't be done...

[atif]

It can be done. I'm pretty sure. I deal with this everyday. When you copy and paste it into another excel file, make sure you paste it as "Paste Special" with 'Values' option turned on. It should not give you #REF sign anymore. hope this helps. let me know

[atif]

Windows("MyFile.xls").Activate
Range("H:H").Select
Selection.Copy
Windows("MyFile_test.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

hope it helps....

[Dmack]

I know that Paste special works... That's my problem though... I am not the end user of the files and programs I write... The employees in my group are and they tend not to listen and go about cutting and pasting all over the place... Causeing a lot of manual checks on my part to repair the damage...


Can you set it so that paste always enters paste special and does values instead????

[jestes]

mnuEditPaste.Enabled = False

[atif]

How can they copy and paste themselves when they are using your app. You mean to say, everybody does thier own copy / paste and re not using proper VB GUI screen which does that for them? Because a simple routine can be created and everybody can just run that particular .exe and don't have to worry about it. But oh well. paste it AS special as a default. I'll get back to you for this one....let me check in other words wait!

[Dmack]

O.K. that works great for the menu bar.... How can I deactivate it for the right click?

Jestes that is great for the one step..

[Dmack]

How could I write an add-in that on right click would give them a msg that states this option not active and returns them to the sheet?