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.



In article ,
"Alan" wrote:

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.

"JE McGimpsey" wrote in message
...

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.


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.

No. You can only use the Application defined events.

In article ,
"Alan" wrote:

Do you think the WithEvents route has any legs?

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?


In article ,
"Alan" wrote:

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!

"JE McGimpsey" wrote in message
...

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?


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.



In article ,
"Alan" wrote:

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.

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

"Alan" wrote in message
...
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.

"JE McGimpsey" wrote in message
...

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.


I like that approach, and as you rightly point out, it makes
transgressions deliberate rather than accidental or just lazy.

I think I may also have to bite the bullet and use OnTime to reset the
settings every 5 seconds.

Thanks,

Alan.

"Jim Cone" wrote in message
...

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


Thanks Jim - I like that.

Alan.