jungle
11-19-2007, 01:22 AM
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
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