Re: MoveAfterReturn is tricking me.

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 10/05/04 

Date: Tue, 5 Oct 2004 09:09:24 -0400

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. 

-- 
Regards,
Tom Ogilvy
"Ken McLennan" <kenrmcl@dnet.net.au> wrote in message
news:MPG.1bcd2e3638e022109896af@news.veridas.net...
> 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  work*** 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 *** 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.
>
>
>
>
Loading