How to contorl drag and drop function for a particular xls

[arunvmohan]

Dear Forum,

I have created one xlt and i have used different macros and funtions in that.

And aslo i have used functions like COUNTA using cell reference. Hence if we use move operation into a cell which refernced in the fuction, then it is giving an error.

In the fuction '#REF!' is coming in place of that cell reference

As i want use this in differnet machines, i cant disable the drag and drop function using Tools -> Options.

Can anybody give me a solution on the above problem.

Thanks in advance


Best Regards,
Arun V. Mohan

[Carim]

Hi,

To get a precise answer, could you be kind enough to share with us the Counta() formula which is giving you problems ...

Carim

[arunvmohan]

I wrote the function as COUNTA(B80,K80,T80,AC80,AF80,AJ80,AN80,AQ80)

When I moved One Cell to the Cell B80 then the function is showing as COUNTA(#REF!,K80,T80,AC80,AF80,AJ80,AN80,AQ80).


So i would like to disable the drag and drop operation.

Best Regards,
Arun V. Mohan

[Carim]

Hi,

Could you have in cell B80

= sourcecell ?

Carim

[arunvmohan]

hi,

Actually i want to restrict drag and drop function only in my xlt.

so could you please give any idea in that.


Best Regards,
Arun V. Mohan

[Carim]

Hi,

Protect your cells and then Protect your sheet ...

HTH
Carim

[dominicb]

Good afternoon arunvmohan

An alternative approach to protecting the sheet :

Please Login or Register  to view this content.

The above code, to be placed in the ThisWorkbook module of the VBE will suppress the drag-and-drop functionality.

HTH

DominicB

[Carim]

Thanks DominicB !!!

I did not know this instruction ...

Cheers
Carim

[arunvmohan]

Thnks Mr. DominicB.

It is working. Can we control copy and paste also in that way?

Mr. Carim thnks for ur help also.

Best Regards,
Arun V. Mohan

[dominicb]

Hi arunvmohan

Alas no! T'would be nice if you could. It is possible to do via VBA but there is no single instruction to do so - you have to disable menu items, toolbar icons and shortcut keys. Ivan F Moala has provided a nice routine on his oages to do (and undo) this for you :

http://www.xcelfiles.com/VBA_Quick13.html

HTH

DominicB

[Carim]

If I may add a word of caution to Dominicb's excellent suggestion ...

Given your situation, where you are providing your worksheet to many other users, you have to be very careful when manipulating XL command bars, because your users could easily find themselves lost and stuck with their own XL no longer operating "the usual way" ...

HTH
Carim

[dominicb]

Hi Carim

I gave up giving that kind of advice a while ago. Why? 'Cos people don't listen and just go ahead and do it anyway. I always try and avoid changing the front end drastically in any apps I put together as people can always mess up a spreadsheet no matter what "safeguards" you put up and if someone is determined enough can circumvent Excel's protection anyway.

All the same, sound advice Carim ...

[dominicb]

Hi arunvmohan

With Carim's warning words ringing in my ears, I've just had another thought ...

A while ago I wrote a short routine for a question posted on another board I answered. This routine basically checked the clipboard every x seonds and emptied it if there was anything there. Somebody wanted to prevent a user doing print screens and there doesn't seem to be a way of doing this.

This routine would achieve the result you wanted without compromising you menu structure. If you are interested I'll try and dig it out ...

HTH

DominicB

[Carim]

Hi Dominicb,

I already knew from your posts that you were a wise and experienced person ...
In addition you are right, based on my experience too, people do not listen to such warnings ... (shame...)


Cheers
Carim

[MrShorty]

Here's a thought that should solve the original problem, but may create a whole host of new problems.

The problem is that, when you cut and paste (or move) cells around, Excel tries to figure out what to do with cell references. Sometimes it doesn't do so well. Using the INDIRECT function blocks Excel from trying to correct references. So:
=COUNTA(INDIRECT("B80"),INDIRECT("K80"),...) would allow someone to cut and paste over those references without causing the reference error.