Entering cell comments using VBA

[kualjo]

I'm trying to learn the ins and outs of entering cell comments using code, but am really struggling with it. My data is on one worksheet, and I am OK with defining values in the designated cells. I am OK with telling VBA where to enter the comments and what the text should be. The problems I am having are with resizing the comment window to fit its contents, and entering additional code when the data requires more, i.e., one or more lines of data require entry into the same cell, and all need the same information. I recorded the resizing part and copied it into the other code, but it gives a runtime error and doesn't resize.
Is anyone familar with any kind of resources that may have been put together specifically for cell comments? There are a number of things that can be done with them, and it would help me immensely if I could get to something like this.

[mdmackillop]

Can you post a sample demonstrating your requirements.

[kualjo]

Sorry it took so long for me to get back. Here is the code that doesn't seem to run the way it should.

Selection.ShapeRange.ScaleWidth 1.07, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.56, msoFalse, msoScaleFromTopLeft

I'd also like to be able to scale the comments based on the amount of information that goes into them. Can the scale width and height be adjusted via code?

[mdmackillop]

see post #2

[kualjo]

Sorry, I thought it was the troublesome code you were asking for. The cell data that I have is just several columns of data similar to the following.

EFGHIJKL2SKUETDETAVesselPOSOInvoiceQty3ABC1235/14/20106/8/2010SS Lollipop4800480012345612345000

The difficulty is in sizing the newly created comment. The standard size is too small for all of this information. And since there may be more than one row of data that could wind up in a single comment, I need to be able to tell VBA how big the comment should be based on how much info it holds.

This is the full procedure (less the sizing commands):

Sub comment_text()
Dim sku As String
Dim etd As Date
Dim eta As Date
Dim vessel As String
Dim po As String
Dim so As String
Dim inv As String
Dim qty As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("Data").Select
Range("E3").Select
Do Until ActiveCell.Value = "" 'sets the values for each data point
sku = ActiveCell.Value
etd = ActiveCell.Offset(0, 1).Value
eta = ActiveCell.Offset(0, 2).Value
vessel = ActiveCell.Offset(0, 3).Value
po = ActiveCell.Offset(0, 4).Value
so = ActiveCell.Offset(0, 5).Value
inv = ActiveCell.Offset(0, 6).Value
qty = ActiveCell.Offset(0, 7).Value
Worksheets("Compressors").Select 'goes to the worksheet where the data will be entered
Range("B2").Select
Do Until ActiveCell.Value = sku 'finds the correct SKU starting cell
ActiveCell.Offset(0, 1).Activate
Loop
Do Until ActiveCell.Value = eta + 7 'finds the correct date
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = qty 'enters the quantity into the cell
With ActiveCell.AddComment 'adds the comment to the cell
.Visible = False
.Text "Vessel - " & vessel & Chr(10) _
& "PO - " & po & Chr(10) _
& "SO - " & so & Chr(10) _
& "Invoice - " & inv & Chr(10) _
& "ETD - " & etd & Chr(10) _
& "ETA - " & eta & Chr(10) _
& "Qty - " & qty
End With
Worksheets("Data").Select
ActiveCell.Offset(1, 0).Activate
Loop
Application.Calculation = xlCalculationAutomatic
End Sub

[kualjo]

Just not getting anything right, am I? That cells example I added didn't work. Basically, it's a varying number of rows in 8 columns starting in cell E3. If this is not enough information and there is some way I can include more, please let me know.