 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Another to get hard returns in instead of: " & Chr(10) & "
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. :oD Use vbCr instead of Chr(10) to create a hard return. > 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 [quoted text clipped - 10 lines] > > Thanks. :oD > Use vbCr instead of Chr(10) to create a hard return. I think you meant to type vbLf , not vbCr. Rick >> Use vbCr instead of Chr(10) to create a hard return. > > 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!! :oD 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 > 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 [quoted text clipped - 10 lines] > > Thanks. :oD 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. :oD > Very likely you have a missing reference, in Tools / References look for a > ref marked MISSING. [quoted text clipped - 23 lines] >> >> Thanks. :oD 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.  SignatureCordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) > 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 [quoted text clipped - 53 lines] >>> >>> Thanks. :oD Just to add, for some reason functions from the Strings and DateTime libraries are particularly susceptible to being 'lost', unless fully qualified. > > At any rate, tried the alternative suggested of vbCr Actually I suggested VBA.Constants.vbLf Regard, Peter T > 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 [quoted text clipped - 57 lines] > >>> > >>> Thanks. :oD >Just to add, for some reason functions from the Strings and DateTime >libraries are particularly susceptible to being 'lost', unless fully [quoted text clipped - 4 lines] >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. :oD >Regard, >Peter T [quoted text clipped - 68 lines] >> >>> >> >>> Thanks. :oD |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.