Prevent drag and drop of a worksheet using Application.OnKey or simila

[maxcat99]

Hi

I'm trying to stop a worksheet being copied by preventing the use of ctrl + mouse click to drag and drop it to a new workbook.

Is it possible to capture 'mouse down' and use it with Application.OnKey?

Thanks

 

[SkipVought]

Hi,

Why not protect the sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxcat99]

Unfortunately Excel worksheet protection is too easy to crack

 

[combo]

It's even easier to disable macros.

combo

 

[maxcat99]

...I need macros to run

 

[combo]

I meant that any user can disable macros and open the file. Macros will not start and copying will ststay enabled.

The standard way of securing excel based applications is to leave a dummy sheet that will be visible when macros are disabled. Otherwise the code manages the access to parts of protected (or no) worksheets. You can use protection set by code with with password and UserInterfaceOnly set to True. This allows access by code to protected sheets (need to be set every time a workbook is opened). The visibility of a sensitive sheets can be set to xlVeryHidden, only vba san access them.

Excel worksheet/workbook does not have strong protection evcept of the file access password. There are methods to disable macros or events when you open a file or run its code, break vba code protection. Leave excel if you don't accept it.

combo

 

[maxcat99]

Thanks

I have a dummy sheet and use xlVeryHidden etc. I'd like to improve security by preventing the use of ctrl + downmouse if that is possible.

 

[combo]

Have you tried worksheet protection with selection of locked cells disabled?

combo

 

[maxcat99]

Yes, it's well locked down but I would like to improve the security by preventing a worksheet being dragged/dropped by stopping Ctrl + mouse down or just mouse down.

I can intervene to stop Ctrl being used by using Application.OnKey but that captures keystrokes not mouse clicks. So I guess I need the equivalent of .OnKey for mouse clicks, if there is one!

 

[SkipVought]

If you cannot select, you cannot drag & drop!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maxcat99]

Thanks SkipVought but the worksheet password can be broken with the appropriate software, hence my desire to prevent the drag/drop....but I guess it cannot be done with Application.OnKey (or similar) to surpress ctrl + mouse down?

Thanks everyone

 

[combo]

I repeat: excel is not secure platform to build worksheet based application. If you allow to open worksheet, it's not a big problem for an advanced user to break all protections. Excel developer has to accept limited security of his applications.

In your case, I imagine that the drag&drop will be disabled/enabled by the master workbook, otherwise (if possible for excel application itself) you will loose a part of excel functionality. In this case in VBE window the user can change the OnKey settings, disble events or macros. If you lock VBE, the same can be done in Workbook_Open event macro in other workbook. Your workbook will loose macro power, excel will get its regular features.

combo

 

[maxcat99]

Thanks Combo