Disable drag / copy functionality - Possible?

[Alan]

Hi All,

Using Excel 2000 SP3 (all patches installed to date - 19 Nov 2003).

I would like to be able to disable the drag / copy functionality that
exists in Excel by default.

I may have used the wrong terminology, so for the avoidance of doubt,
this is what I mean:

1) Open a worksheet

2) Type in anything to A1

3) Select Al

4) Using the mouse, drag A1 across one or two columns using the little
black square that is in the bottom right hand corner when A1 is
selected.

5) This copies A1 across, or creates a series from A1 across (doesn't
matter which for my query).


*Background*

Basically, I am trying to have some control over a worksheet (data
sheet). I can disable copy / paste fine (since it is often a filtered
table and copy / paste is quite dangerous in that situation), and I
have used validation via VBA (WorksheetChange event) to stop users
entering, for example, past dates into certain cells.

However, they can still get a past date into those cells by dragging
across as outlined above which does not seem to trigger the
WorksheetChange event, or otherwise circumvents that code (see below
for the code I am using).

I need to either:

A) Disable that drag / copy; OR

B) Stop the effect in some other way.


Just ask if you need more details.

Thanks in advance,

Alan.


****************************************

*Code in use to catch past dates*

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHandler:

If Intersect(Target, Range("S4:S9999")) Is Nothing Then GoTo
ErrHandler

Application.EnableEvents = False

NewValue = Target.Value

If ((OldValue < NewValue) * (OldValue > 1)) Then

Target.Value = OldValue

Response = MsgBox("You cannot change the work in date to a
more recent date.", vbOKOnly, "Warning")

End If



ErrHandler:

Application.EnableEvents = True


End Sub

 

[Rob Bovey]

Hi Alan,

Application.CellDragAndDrop = False

will turn off the functionality you describe, be be forewarned that there
are about a dozen or so other features that seem unrelated, but which Excel
considers "drag and drop" and which will also be disabled by this setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Alan]

Application.CellDragAndDrop = False

will turn off the functionality you describe, be be forewarned that
there are about a dozen or so other features that seem unrelated,
but which Excel considers "drag and drop" and which will also be
disabled by this setting.

Hi Rob,

Thank you very much for that.

Out of interest, how did you find that setting?

I searched (obviously not very well) then excel help files but I
didn't find it before.

Also, are you able to list what other functionality is disabled? The
excel help file (when I found it!) was not very, err, helpful.

Thanks again,

Alan.

 

[Rob Bovey]

Hi Alan,

This is a pretty common setting that people want to disable, so I just
know it off the top of my head. In the Excel user interface it corresponds
to selecting Tools/Options/Edit and unchecking the "Allow cell drag and
drop" option. If you know which setting to modify in the Excel user
interface, the easiest way to figure out how to do it in VBA is to turn on
the macro recorder, record yourself doing it in Excel, then look at the code
that gets generated.

I don't know of any comprehensive list of things that are disabled when
you uncheck this item, but basically anything that involves dragging a
cells, row, or columns with the mouse, any use of the fill handle, dragging
page breaks to new locations in Page Break Preview mode, and a bunch more
that I just can't seem to remember at this time of night. <g> If something
that you think should work suddenly quits working after you change this
setting, set it back to True and see if that was the problem. Also, don't
forget to set it back to True before your program ends. Most people expect
this setting to be on, so you don't want to leave it disabled when you're
done.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *