MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro acting like the CTRL key

  Asked By: Daisy    Date: Feb 17    Category: MS Office    Views: 1066

When my Worksheet_Change() handler runs as the result of using the
End Key rather than the Enter Key, it appears to either:

1- "Set" the keyboard CTRL flag/function for the Arrow keys. This
makes the first typed arrow key act like "Ctrl-Arrow".

OR more likely

2- The End key "flag" doesn't just cause data entry, but is also held
through/after the Macro and is allowed to normally modify the Arrow
key function. This is also the same effect as "End, Arrow" (End then
V e r y a n n o y i n g !
I use the Worksheet_Change() event handler Sub to auto correct
values entered. It does a column check and runs the appropriate
section in the Sub to do the correction for data in that column.
[typing the first letter, fills the cell with the whole word, or shows
an error MessageBox]. That all works just fine.

The problem I first saw is that after the sub runs, when I hit an
arrow key to move to an *adjacent* cell, the selection jumps to the
next "data edge" in that direction (the next cell adjacent to a blank
one). This is what "CTRL-Arrow" or "End, Arrow" normally do in a
sheet (Excel 97).

I tried other keys to see if the Ctrl-Key modification held for
them, but it doesn't seem to. I found that this happens even AFTER
typing a Page key which does not act like Ctrl-Page. (Ctrl-Page
changes worksheets)
I think this describes it. Because the code is probably too complex
to post, perhaps this describes it adequately.

Things that may be relevant:
I disable Events while correcting a cell, then ReEnable B4 handler Exit.

The _Change Sub calls other Public Subs in the same Sheet AND/OR in
standard modules. This goes down two levels of call, more or less.

Prior to Sub Exit, I do ActiveCell.Activate Method, otherwise you are
in a "non-select" mode and a cell must be manually selected to do

This effect does NOT carry over into the Macro Editor [Ctrl-Arrow =
Next word]

Thoughts ...?



7 Answers Found

Answer #1    Answered By: Adalhelm Fischer     Answered On: Feb 17

Possibly you could use "Cancel=True" to prevent the normal action of the end

Answer #2    Answered By: Tyreece Thompson     Answered On: Feb 17

Sorry. No luck. I tried it at the beginning of the sub and at the
end. No effect. I found it interesting that the "=" did not pop up
the applicable entries from "intellisense" or whatever it is called -
(like other properties do, yet it does not stumble over the line.

Perhaps a scan through the Ctrl-SpaceBar list might turn up another

Answer #3    Answered By: Drew Lewis     Answered On: Feb 17

I don't think the Worksheet_Change event has the Cancel option.

Answer #4    Answered By: Adalard Fischer     Answered On: Feb 17

without an explicit object, the compiler didn't choke on, simply


But, interestingly enough, Help Sez it is only a property for Command
Buttons - a.k.a. indicating that it cancels [the UserForm, I guess).

Trying some appropriately sounding objects (Application...) resulted
in "Object does not support..." errors.
I just won't do that End thing...

Answer #5    Answered By: Paulette Matthews     Answered On: Feb 17

End followed by an arrow  is a normal key  sequence in Excel that are designed
to function  just like a Ctrl-Arrow and bring you, as you said, to the next
"data edge". It does not do this in the VBA Editor or anywhere else; it's an
Excel spreadsheet-ism.

So if you don't want it to behave that way, I guess your question is: "Is
there some way to disable this functionality?" Because Worksheet_Change or
not, End+Arrow works the way you describe. This is not a bug or anything
like that; it's by design. And again, it would happen that way whether you
have a Worksheet_Change event or not. It has nothing to do with your code.

Knowing that, how would you like to see it handled? Do you want to disable
it under all circumstances in your workbook? Or, knowing that it's by
design, is it OK now? What would you like to see?

Answer #6    Answered By: Wanda Patterson     Answered On: Feb 17

3 or 4 coments in the text below......................

Answer #7    Answered By: Joel Brown     Answered On: Feb 17

This is the same thought I was thinking after
thinging for a while after posting.
I was testing my algorithms and wanted to "stay in the cell" in
order to try all the potential input conditions. I managed test
groups (hardware) for the last ten years or so. Unthingingly, I
continued to do so, then decided to move after an End-keypress. This
is normally not expected, so is clearly a "If it hurts when you do
that, then Don't do that" solution.

This should answer the rest of your questions.

The auto-filled text has only three possible values (Wide, Mid,
Narrow), however, depending on some conditions, only 1, 2, or 3 of
those are valid for a given situation (quite a complex algo). I sense
those conditions and limit the entry  to the appropriate ones -
displaying a MsgBox when user goes astray or suffers from fat-finger
syndrome. I also wanted to make things easy by auto-completing the
entries (fortunately the 1st characters allowed  this). No ammending
the auto-complete allowed.
I could have used Validation, and quite honestly, hadn't thought
of that until just now, but wrestling with modifying the Validation
list probably is no more complex than the array I search to find the
valid combinatins.
The End key  fires the Worksheet_Change Sub just fine, but Return
should normally be used exclusively. I also go to he trouble of
changing the "MoveDirectionAfterReturn" to "vbToRight" since data  is
normally entered in rows. And going back to vbDown upon Exiting Excel.

Hmmm... I'll have to understand this feature:
> If you want to edit the cell contents rather than move
> off the cell you have to press F2.

... But the end  key is a shorter reach on this laptop (:-)

Didn't find what you were looking for? Find more on Macro acting like the CTRL key Or get search suggestion and latest updates.