I've killed the copy process

  • Thread starter Thread starter Ken McLennan
  • Start date Start date
K

Ken McLennan

G'day there One and All,

Back again with another head scratcher.

In my ThisWorkBook object event code I've put in place a routine
to move the cursor down on exit from a cell if that cell is in the first
column. Any other cell has the usual cursor right. There are a few other
lines, but the code for this function is the culprit.

If I 'rem' it out, then copy works fine. If I deploy the code then
on that page I can't copy or cut anything. I can copy from another page
to cells there, but not from. If I select a range I get the "marching
ants" indicator, but when I select another cell to copy to they just
disappear and there's nothing in the clipboard.

I suspect that my code is interfering with the system's method of
using cursor movement in cut/copy procedures, but it's only a guess. In
any case, does anyone know how to get around the problem?

Thanks in advance,
Ken McLennan
Qld, Australia

Here's my code: (Which is paraphrased from suggestions given by Bernie
Deitrick & Tom Ogilvy)

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
' Set directions for cursor movement
On Error Resume Next
If Intersect(Target, Range("rosterBlockHd")) Is Nothing Then
If Sh.Name <> "Roster - Diary Of Duty" Then Exit Sub
If Target.Column = 1 Then
Application.MoveAfterReturnDirection = xlDown
Else
Application.MoveAfterReturnDirection = xlToRight
End If
Else
MsgBox "Sorry, can't select Header Block cells"
Cells(6, Target.Column).Select
Exit Sub
End If
End Sub
 
Hi Ken...

Try to read your code...

If your selected cell is not in the rosterblock..
What happens? It'll give you a messagebox AND navigate to row 6

To have these handlers work only on single cell selections, you'd
normally include a line to check that the user is just navigating,
not making a selection...

Make this your first line.

If Target.cells.count >1 then exit sub






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Lots of macros kill the clipboard.

If you toss in enough message boxes/debug.prints, you can see where it's
happening:

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
' Set directions for cursor movement
On Error Resume Next
MsgBox Application.CutCopyMode & " A"
If Intersect(Target, Range("rosterBlockHd")) Is Nothing Then
MsgBox Application.CutCopyMode & " B"
If Sh.Name <> "Roster - Diary Of Duty" Then Exit Sub
If Target.Column = 1 Then
MsgBox Application.CutCopyMode & " c"
Application.MoveAfterReturnDirection = xlDown
MsgBox Application.CutCopyMode & " D"
Else
MsgBox Application.CutCopyMode & " E"
Application.MoveAfterReturnDirection = xlToRight
MsgBox Application.CutCopyMode & " F"
End If
Else
MsgBox Application.CutCopyMode & " G"
MsgBox "Sorry, can't select Header Block cells"
MsgBox Application.CutCopyMode & " H"
Cells(6, Target.Column).Select
MsgBox Application.CutCopyMode & " i"
Exit Sub
End If
End Sub

A cutcopymode of 0 means that it's been turned off.

In my tests, it was on before the moveafterreturndirection line, but off right
after.

I don't know of any workaround (except not using the code!).
 
G'day there keepITcool,
If your selected cell is not in the rosterblock..
What happens? It'll give you a messagebox AND navigate to row 6
Click to expand...

Well, I got that bit right then. That's what I want it to do. The
Header Block contains at least 58 formulae that I want my users to keep
well away from. However, if I protect the sheet then for some strange
reason the scroll goes haywire and I can't select most of the cells.

This way my users can't access the formulae, and I don't have to
figure out why protect is strange.
To have these handlers work only on single cell selections, you'd
normally include a line to check that the user is just navigating,
not making a selection...
Click to expand...

I'd not thought of that...
Make this your first line.

If Target.cells.count >1 then exit sub
Click to expand...

OK. I gave that a try and, unfortunately, it made no difference at
all.

However, I just realised that I may have the code in the wrong
place. Hang on for a minute...

OK, that seems to work fine. I've now moved the code from the
ThisWorkBook area to the codepage for the sheet it's supposed to work
on. Don't ask me why I had it in ThisWorkBook to start with. It was only
ever supposed to work on the first sheet and so should have been there
from the start.

Thanks very much for your help, keepITcool. Much appreciated.
 
This procedure: Workbook_SheetSelectionChange
should be behind the ThisWorkbook.

If you changed the procedure name and it worked fine, I'm surprised--but happy
for you.

If you didn't change the procedure name, then I think you have more testing to
do.
 
Back
Top