Need VBA coding for carriage return

I am trying to create a macro that will call another macro only when a carriage return is detected in a specific cell. The carriage return is a constant and unfortunately I do not know the VBA coding syntax required to handle a constant. Here is a sample of what I developed (which does not work). Any help will be appreciated.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Welcome to the Forum rogers1944 !

First, Target is input to the Sub and you are wiping it out. It tells you what cells changed. You need to test to see if D85 is the cell that changed.

Second Target is a Range, and Range has no method or property called Const.

Third, your code seems to be looking to see if the cell has nothing but a carriage return. Do you really want to see if the cell contains some text that includes a carriage return?

And last, you probably want to also look for a line feed, which is what you get if you type ALT+ENTER into a cell.

Please try this and see if it does what you want.

Please Login or Register  to view this content.

There is nothing wrong with the code in post #2 so any problem is in the implementation - attached workbook has code in the Change event of Sheet1 as an example (Displays a message box rather than calling a procedure). Other event procedures are not affected by this, but there is no 'Worksheet-Click' event.

...but also found that the F8 debug would not function on the Change code.

If you mean you cannot simply run the procedure by stepping through it using F8 then you cannot do that if the procedure has any parameters declared but you can add a Break-point (F9) on any executable line to display the code before the line with the break-point executes.

Hi rogers,

Perhaps you need a SelectionChange event instead of WorksheetChange? See the attached and save it to your hard drive before testing it.
Macros not enabled yet.xlsm

Hi rogers,

Excel does what it does and we mere mortals need to figure out why we don't understand what it is doing. The SelectionChange event doesn't fire if you don't make a change in the cell. IE - you need to change something in D85 to have that event fire.
Then if you look at every character in the string you don't have a CRLF character in D85. I've created a character by character list of the string in D85 and you only have a Char(10).
I think you get those line breaks in an Excel cell by doing Alt+Enter. Us mortals would assume it is a CRLF but I think it is only a LF??? (or is that only a CR??)
Macros not enabled yet 2.xlsm

I'm not sure that's clear.

If you hit ENTER, the content of the cell is committed and the cursor moves to the next cell. There is no carriage return in the cell. This action can be detected with the Change event.

If you are editing a cell and hit ALT+ENTER, a carriage return (line feed, actually) is inserted into the text and the cell remains open for editing. There is no event to be detected at this point.

Can you please elaborate on your original request, that you take an action "when a carriage return is detected in a specific cell"?

Hi rogers,

I gave you the code to display a message when you press enter. It is a Selection_Change event not a Worksheet_Change event (if you changed something in the cell.

Set a breakpoint in both Events in my code in my attached file above and see which of the two event codes is run when you change D85 and press Enter/Return.

Originally Posted by rogers1944

What I would like to accomplish is to enter or edit data in a specific cell and then hit the Enter key. Upon the Enter key being depressed, I would then like to start a macro

This is not detecting a carriage return in the cell, this is detecting a change to the cell, hence the Change event will be fired.

Originally Posted by MarvinP

I gave you the code to display a message when you press enter. It is a Selection_Change event not a Worksheet_Change event (if you changed something in the cell.

He will get the Selection_Change event when he selects the cell, then the Change event when he hits ENTER, then the Selection event again when the selection is automatically moved to the cell below after he hits ENTER.

From the description, it sounds like rogers1944 wants to run a macro when the value in D85 is changed by an edit. So the Change event is appropriate, therefore my code should work.