Solved: Image in Comment box problem that Microsoft couldn't help with

[jungle]

Here's what I'm trying to do in Excel 2003:
I have a routine that opens up the comment box when the user double-clicks that particular cell.

The open comment box displays a .gif image (each image is unique to that particular cell).

The images are all 407.5 px in width; they vary in height, ranging from 800-1200 px.

My problem:
When the comment boxes open, some of them get randomly proportioned; the size of the comment boxes are all uniform, but the image within them get resized.

The result is distortion of the images.

I spend a couple of hours on the phone with Microsoft tech support, clicking through the various 'lock aspect ratio' buttons, but nothing has worked.

What I think might work:
Double-clicking on the cell to open the comment box.
Then, after the comment box is open, resizing the size of the box.

This is the code I'm using to double-click on a cell that results in opening the Comment Box:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo ExitSub
If Range(Target.Address).Comment.Visible = True Then
Range(Target.Address).Comment.Visible = False
Else
Range(Target.Address).Comment.Visible = True

End If

ExitSub:
ActiveCell.Offset(0, -1).Select

End Sub

And here's code that will resize a comment box to my specific width and height (the dimensions need to be modified for each particular image):

Sub comment_resize()
With ActiveCell
.Comment.Shape.Visible = True
.Comment.Shape.Select True
.Comment.Shape.IncrementLeft 4
.Comment.Shape.IncrementTop 5
.Comment.Shape.Height = 1238.4
.Comment.Shape.Width = 407.5
.Comment.Shape.Visible = msoTrue
End With
End Sub

I'm new to VBA; is there a way to create a macro for each cell with a comment and then have a subroutine that resizes that comment box once it's open? (It doesn't matter to me if it uses any or part of the above code, as long as it works!)

Much thanks in advance; I've spent two entire days struggling with this.

George

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const COMMENT_HEIGHT As Long = 100
Const COMMENT_WIDTH As Long = 200

On Error GoTo ExitSub
With Target.Comment
If .Visible Then
.Visible = False
Else
.Visible = True
.Shape.Height = COMMENT_HEIGHT
.Shape.Width = COMMENT_WIDTH
End If
End With

ExitSub:
End Sub
[/vba]

[jungle]

Wow! Great.

This works to both double-click open the comment box and resize it to whatever I set in:
Const COMMENT_HEIGHT As Long = 100
Const COMMENT_WIDTH As Long = 200

I'll need to change the height and width settings for each different comment box.

So, now my question is:
How do I set this up so that each individual cell that has a comment box has its own macro? (I'm new to VBA).

George

[Bob Phillips]

What we can do is define 3 string constants of the cell addresses, comment heights, and comments widths, and parse these

[vba]

rivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const CELL_ADDRESS As String = "H1,H2,H3,M1,M2,M5"
Const COMMENT_HEIGHT As String = "100,120,130,80,70,20"
Const COMMENT_WIDTH As String = "200,200,200,50,50,60"
Dim aryAddress
Dim aryHeight
Dim aryWidth
Dim Pos As Long

On Error GoTo ExitSub
aryAddress = Split(CELL_ADDRESS, ",")
aryHeight = Split(COMMENT_HEIGHT, ",")
aryWidth = Split(COMMENT_WIDTH, ",")
With Target.Comment
If .Visible Then

.Visible = False
Else

.Visible = True
On Error Resume Next
Pos = Application.Match(Target.Address(False, False), aryAddress, 0)
On Error GoTo ExitSub
If Pos > 0 Then

.Shape.Height = Val(aryHeight(Pos - 1 + LBound(aryAddress)))
.Shape.Width = Val(aryWidth(Pos - 1 + LBound(aryAddress)))
End If
End If
End With

ExitSub:
End Sub
[/vba]

[jungle]

xld,
This works brilliantlly!

You solved in a few minutes what Microsoft couldn't help me with!

Now, one more refinement:
The width of the comment boxes is going to be the same for all boxes at 407.5 pixels.

How can I modify this, so I only need to type in "407.5" one time, yet have it apply to all the boxes referenced?
Const COMMENT_WIDTH As String = "200,200,200,50,50,60"
aryWidth = Split(COMMENT_WIDTH, ",")

Thank you very, very much!!! (though I don't drink!)

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const CELL_ADDRESS As String = "H1,H2,H3,M1,M2,M5"
Const COMMENT_HEIGHT As String = "100,120,130,80,70,20"
Dim aryAddress
Dim aryHeight
Dim Pos As Long

On Error Goto ExitSub
aryAddress = Split(CELL_ADDRESS, ",")
aryHeight = Split(COMMENT_HEIGHT, ",")
With Target.Comment
If .Visible Then

.Visible = False
Else

.Visible = True
On Error Resume Next
Pos = Application.Match(Target.Address(False, False), aryAddress, 0)
On Error Goto ExitSub
If Pos > 0 Then

.Shape.Height = Val(aryHeight(Pos - 1 + LBound(aryAddress)))
.Shape.Width = 407.5
End If
End If
End With

ExitSub:
End Sub
[/vba]

[jungle]

This works beautifully!!!
Many, many thanks!

George

[jungle]

Well, now I've got a related problem...

The above solution worked beautifully until recently...

Out of the blue, I now have comment boxes that are text only that are resizing randomly (for many months, they stayed the appropriate height and width that matched the text).

Now, I need to control the size of comment boxes that are text only (the comment boxes that the above code addresses contained JPEG images).

I've gone back and used the above code to change the width variable for EACH comment box.

But now the problem I have is that when I double-click on a cell to open the comment box, I get an error window that I didn't use to get:
"The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect command..."

I don't get what I'm doing wrong; the spreadsheet used to work beautifully, even with the worksheet protected.

I'm stuck again.