Excel Comment: Inserting a Carriage Return

Running VB code to add comments to a range of cells.

The resulting comments look like this (all on one wrapped line):
"Hatters Pond Field Mobile, AL".

I'd rather the comment was on two lines:
"Hatters Pond Field
Mobile, AL"

If I was typing the comment manually, I'd add a carriage return to get
the comments on two lines.  However, I'm running the code below.  Any
suggestions?
Thanks, Mark

Private Sub CommandButton2_Click()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("MB BUYERS")
Set sh2 = Worksheets("PROJ")
Dim cell As Range
For Each cell In sh1.Range("B6:B54")
  On Error Resume Next
    cell.Comment.Delete
  On Error GoTo 0
  cell.AddComment Text:=sh2.Cells(cell.Row - 4, 5) & _
   " " & sh2.Cells(cell.Row - 4, 6)
    cell.Select
    ActiveCell.Comment.Visible = True
    cell.Comment.Shape.Select True
    Selection.ShapeRange.ScaleHeight 2#, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.22, msoFalse,
msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.7, msoFalse,
msoScaleFromTopLeft
    cell.Select
    ActiveCell.Comment.Visible = False
Next

End Sub
0
mroach (7)
9/28/2003 1:34:27 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
675 Views

Similar Articles

[PageSpeed] 44

How about:

  cell.AddComment Text:=sh2.Cells(cell.Row - 4, 5) & _
   vblf & sh2.Cells(cell.Row - 4, 6)



Mark wrote:
> 
> Running VB code to add comments to a range of cells.
> 
> The resulting comments look like this (all on one wrapped line):
> "Hatters Pond Field Mobile, AL".
> 
> I'd rather the comment was on two lines:
> "Hatters Pond Field
> Mobile, AL"
> 
> If I was typing the comment manually, I'd add a carriage return to get
> the comments on two lines.  However, I'm running the code below.  Any
> suggestions?
> Thanks, Mark
> 
> Private Sub CommandButton2_Click()
> Dim sh1 As Worksheet
> Dim sh2 As Worksheet
> Set sh1 = Worksheets("MB BUYERS")
> Set sh2 = Worksheets("PROJ")
> Dim cell As Range
> For Each cell In sh1.Range("B6:B54")
>   On Error Resume Next
>     cell.Comment.Delete
>   On Error GoTo 0
>   cell.AddComment Text:=sh2.Cells(cell.Row - 4, 5) & _
>    " " & sh2.Cells(cell.Row - 4, 6)
>     cell.Select
>     ActiveCell.Comment.Visible = True
>     cell.Comment.Shape.Select True
>     Selection.ShapeRange.ScaleHeight 2#, msoFalse, msoScaleFromTopLeft
>     Selection.ShapeRange.ScaleWidth 1.22, msoFalse,
> msoScaleFromTopLeft
>     Selection.ShapeRange.ScaleHeight 0.7, msoFalse,
> msoScaleFromTopLeft
>     cell.Select
>     ActiveCell.Comment.Visible = False
> Next
> 
> End Sub

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2003 2:24:59 PM
Reply:

Similar Artilces:

why wont an excel doc open with new msn office
i have just got a new comp with the new microsoft woeks on it, word doc if differant, and a few other things but an excel doc was sent to me an it will not open it ???????? Hi MS Works is a 'light' version of Office, containing a 'light' version of Excel and a 'light' version of Word. You cannot open Excel or Word files with Works. You'll have to get the files saved as .csv or .txt or a Works file to be able to open them with Works. Andy. "peterk4" <peterk4@discussions.microsoft.com> wrote in message news:53597904-F529-4E67-BC56-D957C375779...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

Stored procedure wont insert Fields, RadioButtonLists and CheckBox
Hello Community I have a CheckBox and a RadioButtonList and fields on an aspx web page form. I pass the fields on the form to a method that creates the parameters and executes a stored procedure and the stored procedure is supposed to insert this information as a row into a table. For some reason the stored procedure executes but the row does not get inserted. I can’t see why the row doesn’t get inserted especially since I put BreakPoints in the code all along the way and I see the values just as they get passed into the stored procedure but maybe someone looking at this ...

Excel 2002 Create Workbook (merge/reformat)?
I have 12 seperate spreadsheets I want to combine into 1 workbook (12 tabs). Any other alternative ways to do this other than copy and paste? Need to reformat "most" of the worksheets, I assume this can be done if all 12 tabs are selected. I may have to do this several times otherwise I would just copy and paste. Each spreadsheet has an average of 3k-4k transactions. You can open the workbook, ctrl-click on the worksheet tab and drag that worksheet into another workbook. (Then close that "sending" workbook and open the next...) ~Dave~ wrote: > > I have 1...

Excel 97-remove link but keep data
Hi guys, I have a pre-existing excel 97 s/s which has links to other s/s's. I would like to keep the data only (like paste-special, values) and remove the linked reference from showing in the current s/s. Any ideas how to do this instead of me changing each linked reference? Thanks in advance! search for .xls or [ or ] to see if you can find the formulas that contain links to other workbooks. Van wrote: > > Hi guys, > > I have a pre-existing excel 97 s/s which has links to other s/s's. I would > like to keep the data only (like paste-special, values) and rem...

Excel file takes a long time to save
I have a 1,000 x 15 straight worksheet with no formulas - about 100K. I went to auto-format a pattern, but didn't select the area and if formatted the entire spreadsheet. It's since gone from 5 seconds to 60 seconds per save and I can't reverse this, even by changing the auto-format setting to "none." I'm backed-up, but the current version is about three hours ahead and it will take longer to retrace the work by hand. Any suggestions? Thanks, Ben Try resetting the last used cell. Debra Dalgleish has some techniques at: http://www.contextures.com/xlfaqApp.html#Un...

Advance Filter Return
Hello, I've created an Adv. Filter that copies data from Sheet2 to Sheet3 (headers included), the issue is that I would like to give the ability to the user to select which record from the possible multiple that populated from the Adv. Filter. Example user clicks column "A" ("Date", Sheet3) and on the record / ROW he or she is needs to be populate back to Sheet1 for editing. I do have a �unique record� tie this all together which date & time (merge, column "F" as text) that would be like my lookup unique record, but I simple cant think of a way to ge...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

Excel design questions
Hi, I'm a new to excel. I'm assigned task to perform creating charts based on the following data. I explored little bit from web about the capabilities of excel, but few areas I'm not still quite sure about how to get it working. - Is it possible to have advanced filter to automatically refresh if the data is changed on which the filter is acting on?? Do I need to write maro for this?? - If i have a column with data (different types of tree names, ex: Apple, Apple, Maple, Grape,Grape..etc - name is repeatable). Can I uniquely get the name of all the different tree names automati...

how to control the size of a comment
hi, i wonder how to control the size of a comment i added in a cell with vba. i failed all, thanks For an individual comment, this is one possibility: With ActiveCell.Comment .Shape.Width = 200 .Shape.Height = 100 End With For all comments on a sheet: Dim c As Comment For Each c In ActiveSheet.Comments c.Shape.Width = 200 c.Shape.Height = 100 Next William ...

Excel 2000 #9
Is there a way to change the date format in footers and headers.?.these two items do not seem to pick up the formatting specified for the individual cells. David H. David Excel in any version uses the Short Date format from Windows Regional settings in headers/footers. You can make some minor changes in Regional settings but not too much. How about a macro to add the date in any format you choose? Sub DateInFooter() With ActiveSheet .PageSetup.CenterFooter = Format(Date, "dddd, mmmm-dd-yyyy") End With End Sub Gord Dibben MS Excel MVP On Tue, 5 Feb 2008...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Multi-User Excel File on a Network Drive
How can I make an Excel file accessible to multiple users concurrently? With all able to make and save changes to the same file. Thanks, Mike "Mike" wrote > How can I make an Excel file accessible to multiple > users concurrently? With all able to make and save > changes to the same file. Tools / Share Workbook Jordon ...

Inserting Rows in Excel
I have a spreadsheet with a unique identifier for each customer, Customer Information File (CIF). Whenever this number changes, I want to automatically insert a blank row. This will merely making the the spreadsheet more pleasing to the eye to read. Keep in mind, there may not be a break in CIF for 1 to 15 lines, but whenever this CIF number changes, I want a new blank row. Any help would be greatly appreciated!! David David Macro solution OK? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With ...

How do I Insert 2C Mac EPS file into Publisher ?
In commercial printing we get a lot of logo's and other art that is created as a Mac EPS and I cannot seem to insert it into Publisher. What are the correct steps for doing this and for it to display. According to Pub 2003, you need a graphics filter to insert .eps files. The following is copied directly from the Help file: The Encapsulated PostScript (EPS) graphics filter (Epsimp32.flt) supports the Adobe Systems Encapsulated PostScript Specification versions 3.0 and earlier. The filter supports .eps images from Tagged Image File Format (.tif, .tiff) (Tagged Image File Format (TIF...

Use Excel to update Outlook address book
hi all... Is there a way to update outlook address book using excel? Is there a macro available for it? Thank you for your answers Mike ...

Compress an Excel File
I have an excel file that is 36 meg. Do you know how to compress the file? When I zip the file it drops to 3meg so I know it can be compressed. I beleive the file has become large due to the constent changes I need to make to the file. Help Please. Hi Mandy Maybe this will help? http://www.contextures.com/xlfaqApp.html#Unused You can use Rob Bovey's Code cleaner to clean up your code http://www.appspro.com/utilities/utilities.asp -- Regards Ron de Bruin http://www.rondebruin.nl "Mandy" <mangelo@arbys.com> wrote in message news:42c401c47fa1$ddc405d0$a401280a@...

Tracking an email when sent directly from Excel
I often use the feature File>Send To>Mail Reciepient (as attachment) when I'm working in an Excel Workbook. However I can never find any record of this in Outlook, or anywhere else. Is there a way so this gets recorded in Outlook, or can you tell me where I can find some record of the email and its contents. Thank you, On May 18, 7:23=A0pm, Rob <robfl...@sbcglobal.net> wrote: > I often use the feature File>Send To>Mail Reciepient (as attachment) > when I'm working in an Excel Workbook. =A0However I can never find any > record of this in Outloo...

Diable excel macro for DATES
Hi, everytime i copy and paste from a website in this format XX/YY excel sees it as a DATE. how can i disable this feature..I just want t paste it as its raw form of XX/YY. When i cut and paste it in my exce and try to change the format by using TExt. it comes up with rubbis numbers. Thank -- Message posted from http://www.ExcelForum.com try formatting, as text, before you import -- Don Guillett SalesAid Software donaldb@281.com "evo >" <<evo.18vufd@excelforum-nospam.com> wrote in message news:evo.18vufd@excelforum-nospam.com... > Hi, > > everytime i copy ...

Reading Excel Checkboxes in Access
Hi All I created a spreadsheet with checkboxes (controls) on it for reporting purposes. I now need to import the data contained on the spreadsheets into Access. The data is not laid out in neat columns or rows, it was formated to be a stand alone report originally. My problem is not in reading cells within the spreadsheet, I'm able to do that. My issue is that I can't tell if a checkbox is checked or not. What I've done: If I execute the following code in Excel I return a boolean partAM = CheckBox15.Value I return true or false Here is my access code: Set xlapp = New Excel....

How do I add multiple comments to a cell?
I need to add more than one comment to data in a single cell, is this possible? Not possible. deberjones wrote: > > I need to add more than one comment to data in a single cell, is this possible? -- Dave Peterson ...

make excel files with same names to be opened at one time
It is very difficult to work if you want to compare changed data made in the file with old one ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=cde27763-1bc7-4299-874f-ac53e066ae7a&dg=microsoft.public....

Insert Menu
Run Win98 SE, Office 2000. On my Toolbar, File, Edit, View, Insert, Format, etc.. I want to place a Symbol on a word doc or post card I'm working on. When I click on Insert, the drop down only list's: Picture> Clip Art From file Object.. and varied other menue choices EXCEPT..Symbols, ?How? can I place the Symbols choice into the drop down menu for Insert? I do have & am able to utilize Symbols in MS Word w/Office 2000. Regards, PRK 7/23/03 The symbol insert option appears when you have a text box selected in Publisher. -...

Insert New Page and Keep Formatting.
I have a few questions. First, how do I insert a new page in a Newsletter and keep the formatting changes I made to the text. For example I changed my font to different sizes, but when I insert a new page it all resets back to the original newsletter template default font size. How do I prevent this from happening? Secondly, when I do insert a new page how do I add a calender? When I go to Page Content it says that you cannot change page content to pages that were INSERTED only the first 4 pages. Thanks, Matt mjcxp <mjcxp@discussions.microsoft.com> was very recently heard to u...

Why does my excel chart appear in gray when I copy it?
I want to copy an excel chart into powerpoint. It is a simple column chart. However the columns contain a color gradient. When I copy the chart to the clipoard, the columns turn into gray. This problem however does not occur on other computers using the same excel version (2003). Hi batman, There are about 30 ways to copy and paste charts from Excel into PowerPoint. The problem you are describing is not one I have heard of - namely that it works on one machine but not another. However, you could try a different method of copying. For example - you can use the Camera tool to copy a ...