Identify when a user attempts to turn on "Allow cell drag and drop"

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi All,

I have a workbook where we have disabled "Allow cell drag and drop".

I would like to be able to stop users from re-enabling it manually
from the Tools - Options menu.

Is that possible?


I know I can disable it using VBA by the following line:

Application.CellDragAndDrop = False


My problem is that I don't know when it has been changed manually by a
user?

I guess I actually either one of:

1) Stop it happening by disabling it entirely so users cannot
re-enable OR

2) Catch the re-enabling 'event' and trigger code that re-disables it
immediately.


Thanks for any suggestions.

Alan.
 
The only way I an think of is to use OnTIme macros to disable the
preference every so often.

There's no event that fires when a preference is changed, and drag and
drop is not a workbook-level preference, it's an application-level
preference, so you can't really prevent a user from setting it manually
(you *can* remove the Tools/Options or Preferences menu item - they're
in different locations in WinWord and MacWord versions - remove keyboard
shortcuts, prevent access to the VBE, but it's hard if not impossible to
be completely comprehensive).

You could make a Workbook_SheetSelectionChange() macro that sets drag
and drop to false, but that won't prevent the user selecting a cell or
cells, changing the preference, and dragging and dropping *that*
selection.
 
JE McGimpsey said:
The only way I an think of is to use OnTIme macros to disable the
preference every so often.

There's no event that fires when a preference is changed, and drag
and drop is not a workbook-level preference, it's an
application-level preference, so you can't really prevent a user
from setting it manually (you *can* remove the Tools/Options or
Preferences menu item - they're in different locations in WinWord
and MacWord versions - remove keyboard shortcuts, prevent access to
the VBE, but it's hard if not impossible to be completely
comprehensive).

You could make a Workbook_SheetSelectionChange() macro that sets
drag and drop to false, but that won't prevent the user selecting a
cell or cells, changing the preference, and dragging and dropping
*that* selection.
Click to expand...

Hi,

After posting I had an idea that perhaps by using:

Private WithEvents App as Application

in a class module might offer a way to find an event.

However, nothing is listed under there.

I had consdered using OnTime to do it periodically, but in order to
catch someone between turning it on and using it, I would have to fire
it every 5econds or so!

Do you think the WithEvents route has any legs?

Thanks,

Alan.
 
Perhaps if you explained *why* you needed to disable drag and drop
someone might have an idea or two. What's the difference for you between
d&d and cut/paste?
 
JE McGimpsey said:
Perhaps if you explained *why* you needed to disable drag and drop
someone might have an idea or two. What's the difference for you
between d&d and cut/paste?
Click to expand...

Hi,

The workbook is used to track workflow in a services business.

Client name, some other client details relating to the type of job,
date in, date started etc etc through to completion.

Users often filter the list to only show their clients, but we were
getting problems where a user 'dragged' or 'copied' a date (say) from
one row down through what appears to them as being only their clients,
but in fact also contains hidden rows with other clients, thus
creating a data integrity issue.

We disabled D&D and C&P to stop that happening.

Of course, we could just say it is a user training issue, but if
someone forgets just once, and saves the file, then everything can go
to pieces quickly. If they do it accidentally and know that, then
they
just don't save those changes.

The risk was such that we decided it would be best to disable that
functionality.

However, some bright sparks have been turning it back on. That is
okay, and they say 'we know what we are doing', but one of them did
forget, caused some damage, and didn't know it. In fact, they won't
even notice of course - it is someone else's client workflow data that
is damaged.

If you need more background, just ask.

Thanks,

Alan.
 
Well, in part it's a training issue. But it's also a design issue. Since
I can't think of any way to make it foolproof (fools getting more clever
all the time), perhaps one way would be to protect the worksheet
(allowing filtering perhaps), and putting a button on the sheet that
removes that protection, disables drag and drop, and throws up a message
box saying something like "Drag and Drop has been disabled. in the
interest of data integrity, please don't change the Drag and Drop
preferences while editing this workbook". I'd use the
Workbook_SheetSelectionChange event to disable d&d every selection
change as well.

That way, someone would have to deliberately violate your restriction in
order to use d&d.
 
Alan,

You could try playing around with the following code.
Note what happens when a multi-cell selection is moved...
'---------------------------------------------------------
'Code goes in the worksheet code module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CellDragAndDrop Then
Application.EnableEvents = False
Application.Undo
Application.CellDragAndDrop = False
Application.EnableEvents = True
End If
End Sub
'---------------------------------------------------------
Regards,
Jim Cone
San Francisco, CA
 
Back
Top