Another to get hard returns in instead of: " & Chr(10) & "

[StargateFanFromWork]

When I've recorded a macro with text and have done the ^+Enter, the macro
records that as " & Chr(10) & ". The macros I have that do this have
worked for years without any problems. But here, in this new contract,
weird stuff is happening. I now get the error:

"Compile error: Can't find project or library"

The "Ch" in the above " & Chr(10) & " is then shown highlighted in dark
great and the title for the macro is highlighted in yellow.

What can we use in place of ^+Enter to create a hard return in a cell via a
macro, and so that " & Chr(10) & " isn't needed?

Thanks. D

 

[Frederik]

Use vbCr instead of Chr(10) to create a hard return.

 

[Peter T]

Very likely you have a missing reference, in Tools / References look for a
ref marked MISSING.

As a short term fix to get out of trouble until the ref is solved try

VBA.Strings.Chr$(10)
or
VBA.Constants.vbLf

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Use vbCr instead of Chr(10) to create a hard return.

I think you meant to type vbLf , not vbCr.

Rick

 

[StargateFanFromWork]

Knew there had to be some reason why it's worked everywhere but here. Well,
I took a look in the Tools > References of the VBE and the only thing that I
could see out of the list that says "missing" is

MISSING: Microsoft Calendar Contro 9.0

which seems odd. But it's missing. And this references a missing OCX
called MSCAL.OCX. Does this govern something as simple as "carriage
returns" <g>?

At any rate, tried the alternative suggested of vbCr even though I never
have much luck with that one. Sure enough, only got a little box.
So hunted around for what has worked before (I don't have too many VB
message boxes so took me some time to find). I subsbituted the vbCr with
vbCrLf and even though I still get a little box, I at least get a carriage
return inside the cell which is what counts. Fortunately, all this involves
tips or help information that I have assigned as macros to buttons on my
Excel.xlb to give me easy access to 4 things I use over and over and saves
me hunting them down, so the little boxes are a nuisance, but I can live
with them until I fix this problem.

Any suggestions on how to fix this, as I must admit I haven't an idea how to
fix (unless it's the missing MSCAL.OCX that's the culprit and hunting that
down).

Thanks. D

 

[StargateFanFromWork]

I think you meant to type vbLf , not vbCr.

Rick

Oh, there. That _does_ work. Better than what I tried. I get the carriage
returns but no boxes. Yeay!! D

 

[Chip Pearson]

Whenever any library is missing in the list of reference, any other function
in any other referenced library can be "not found" by the compiler. It is
as if a single missing reference screws up the entire system of typelibs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Peter T]

Just to add, for some reason functions from the Strings and DateTime
libraries are particularly susceptible to being 'lost', unless fully
qualified.

Actually I suggested
VBA.Constants.vbLf

Regard,
Peter T

 

[StargateFan]

Just to add, for some reason functions from the Strings and DateTime
libraries are particularly susceptible to being 'lost', unless fully
qualified.


Actually I suggested
VBA.Constants.vbLf

There was another post suggesting just vbCr, not yours here <g>. I
actually don't know how to use the VBA.Constants. bit, so just went
with what I know. Thanks. D