MoveAfterReturn is tricking me.

[Ken McLennan]

G'day there One & All once more,

Up until this evening I was scooting along marvelously with my new
rostering application. However now I've come across one more of those
tricky little traps for the unwary.

I've got the first column of my worksheet checking to see if the
first character of the typed entry is a string or a number. It then does
one of two things. If a string, the entry is considered a section
heading and it's changed to upper case, formatted, row height set and a
border put under the first 7 cells. That bit works perfectly.

A numeric entry, however, is considered the employee number and
opens a userform to gather a few other details such as position & name.
I also collect allowance qualifications. On clicking the "Enter" button,
this data is stored in consecutive columns adjacent the entry cell with
some data going into hidden columns. At least that's the plan.

Because of the way that the event trigger seems to work, I find
that after the code finds a numeric entry, my userform shows and the
selected cell is immediately moved down one row. The form is then
completed and "Enter" clicked, thus storing the data in the row under
the initial point of entry. This is not exactly what I was after.

I've alleviated the situation on my own system by writing the data
to the line above the current selection. However, as my users don't
necessarily have consistent option settings I can't be sure what the
MoveAfterReturn Property will be set to. I've been fiddling about with
turning it off & on at appropriate moments, but it's getting quite
intricate and messy.

I only really need to have it turned off on the first column of
the first sheet but that's asking a bit much =). I tried the obvious
manouvre of turning it off in the Worksheet_Change code but, as
expected, since it's the change that triggers the code the property is
turned off after the selection has moved.

Has anyone seen, or can think of, a workaround? Just turning it
off suits me, but that's not good for other users.

See ya
Ken McLennan
Qld, Australia.

 

[Bernie Deitrick]

Ken,

Don't use the Worksheet_Change but do use the Workbook_SheetSelectionChange
event to check which sheet and which column have been selected, and change
as needed.

Put this in Thisworkbook's codemodule:

Dim myDir As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
If Sh.Name <> "Sheet name where this should happen" Then Exit Sub
If Target.Column = 1 Then
myDir = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = myDir
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

The argument Target in the Worksheet_Change event tells you what cell
triggered the change event. So use the Target to get the proper row rather
than activecell. Put this information in a global variable that can be seen
by your userform and use it to figure out where to write the data.

 

[Ken McLennan]

G'day there Bernie,

Don't use the Worksheet_Change but do use the Workbook_SheetSelectionChange
event to check which sheet and which column have been selected, and change
as needed.

Put this in Thisworkbook's codemodule:

Dim myDir As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error Resume Next
If Sh.Name <> "Sheet name where this should happen" Then Exit Sub
If Target.Column = 1 Then
myDir = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = myDir
End If
End Sub

Thanks for that Bernie. That's an approach I'd not considered at
all. Not only that, but it allows different directions to move in
different locations. Very handy indeed =)

Tom has also offered a reply with a different way of working it.
Rest assured, I'll be having a bash at both of them to see which one I
can adapt to this and other problems that arise.

Thanks once again
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there Tom,

The argument Target in the Worksheet_Change event tells you what cell
triggered the change event. So use the Target to get the proper row rather
than activecell. Put this information in a global variable that can be seen
by your userform and use it to figure out where to write the data.

That's where I mucked it up!! I tried using a variable, but
didn't have it declared in a global variable, just in the userform code.

Bernie also offered a response, and I'm about to rush off and try
both approaches and see what comes out of it all.

Thanks again, Tom.
See ya
Ken McLennan
Qld, Australia