How to insert a crlf?

A

absolutfreedom

Hi, I'm writing a macro for excel. This is what the subroutine i
supposed to do:
- Insert a value into a specified cell
- Move to the next row below it.

Well, but it's kinda tricky, to move the focus to the next row, I'm no
supposed to use something like this: Range("C" & ActiveCell.Row
i).Select

What I'm supposed to do is to insert a "carriage return"/CRL
character, so that the focus will move to the next row (same column).
When we are typing an excel file, notice that when you press "Enter
key, the focus/cursor will move to the next row? Yeap, that's what I'
supposed to do!
I tries to do it using Chr(13) or vbCrlf, but neither works. What the
did is that the new line was inserted in the same cell (i.e. it'
similar like typing inside a cell, and you press ALT + Enter).

Can anyone help?

Thanks!

Adria
 
J

JE McGimpsey

Why can't you use .Select? Is this a school assignment?

The only way to "insert" a CRLF is to use the SendKeys method - but I'd
really discourage that - in my experience it's not particularly
reliable, and it doesn't work cross-platform.
 
E

Earl Kiosterud

Adrian,

The problem isn't with an answer, it's with the question. Excel responds to
the Enter key by moving the cell pointer down. But it doesn't do that by
inserting a CRLF anywhere. It just moves the cell pointer down. Pressing
Alt-Enter while in a cell puts in a line feed, and doesn't move the cell
pointer down (it stays in Enter or Edit mode).

I'd be interested in what the "correct" answer to this is.
 
H

Harald Staff

Hi Adrian

Second the others. This is a nonsense task you're given. Even if you manage
to solve it, it has nothing to do with how programming is supposed to be
done.

I don't want you to use a single minute more on this. So here's a working
solution -if I understood the task correctly that is. If it passes your
test, throw it away, come back to these newsgroups and study some real-life
code and professional programming standards instead.

Sub Test()
Sheets(1).Activate
Range("C5").Select
Application.MoveAfterReturnDirection = xlDown
Application.SendKeys "1234", True
Application.SendKeys "{ENTER}"
Application.SendKeys "{ENTER}"
Application.SendKeys "Adrian rules", True
Application.SendKeys "{ENTER}"
End Sub

HTH. Best wishes Harald
 
A

absolutfreedom

Hi... thanks for all the replies.

Actually, I gotta use "Enter" because the "Enter" will trigger an OnKe
event. While using .Select won't trigger that event
 
H

Harald Staff

The idea of events is that they start a macro when something happens. Since
you already have a macro running and knows exactly what happens in it, you
don't need an event.

HTH. Best wishes Harald
 
C

Chip Pearson

In that case, use

SendKeys "~"

If this is an assignment for a class, tell the instructor that
this is a poor exercise because no real world software would ever
use SendKeys or use an OnKey procedure assigned to the Enter key.
There are other, much better, ways to do this. My guess is that
the instructor knows less about Excel than he thinks he does.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"absolutfreedom >" <<[email protected]>
wrote in message
news:[email protected]...
 
A

absolutfreedom

Thanks for all the input guys. Thanks Chip, SendKeys "~" works.
I know, this is kinda strange (and stupid) assignment.

Anyway, I have encountered something weird about excel macros. Let'
say I have 2 excel sheet (each of them has a method which is triggere
by an OnKey event). If I open any 1 of them, it works fine. However, i
I open both of them at the same time, the macro doesn;t work properly.
Example:
I have A.xls and B.xls
A.xls has a macro which will duplicate the value of cell (1,1) into 1
rows on the event of "Enter" key.
While B.xls has a macro which will duplicate the value of cell (1,1
into 3 rows and then launch a vb form on the event of "Enter" key.
Now, when I open both A.xls and B.xls at the same time, and I wa
working on A.xls first, everything's fine. But when I switch to B.xls
and work on it, the macro for A.xls is still in effect! Thus, on B.xls
when I press "Enter", instead of duplicate 3 rows + launch a vb form
it duplicate 10 rows!
And vice versa (if I work on B.xls first, then A.xls will have th
impact on B.xls's macro)

Has anyone experienced this?

Thanks

Adria
 
J

JE McGimpsey

Has anyone experienced this?

Sure - OnKey is a global setting. If you want it to be workbook
specific, then you need to use the Workbook_Deactivate and _Activate
events to turn it off and on when you switch workbooks (Put these in the
ThisWorkbook module, along with the Workbook_Open() code):

Private Sub Workbook_Deactivate()
Application.OnKey "~"
End Sub

Private Sub Workbook_Activate()
Application.OnKey "~", "MyMacro"
 
A

absolutfreedom

Got it! Thanks a lot!
One quick question: How to bring a non-active excel sheet on top? (i.e
make it the active one)?

Now let's say I open 2 excel files (A.xls and B.xls). Now, whenever
received an input, I have a formula which will be triggered by an OnKe
"~". That formula/module will check whether that input belong to A.xl
or B.xls. So if I enter an input into A.xls and it does belong t
A.xls, nothing happen. However, if I enter an input to A.xls, an
actually that input belong to B.xls, I have to delete that input fro
A.xls, and fill it to B.xls.
I got everything working, but I can't bring B.xls on top/active. S
what my program does right now, it only fill B.xls (which is still o
the back). How to make it on top/activate it?
I used this:
Workbooks("B.xls").Activate
Workbooks("B.xls").Sheets(1).Range("C" & Line).Select
ActiveCell.Value = inputvalue

Any suggestion? Oh yeah, and also when I try to SendKey "~", it sen
the Enter key to A.xls. Actually I want it to be sent to B.xls.

Thanks

Adria
 
Top