Resize comments with only picture.

[Joergen Bondesen]

Hi NG

I'm using comment to displaying pictures.

Is it possible to resize comments to the size of the picture, by using a
macro?

 

[Peter T]

There are two approaches, read the image size from file or assign the image
to an autosize image control are read its dimensions. Examples of both in
this thread -

http://groups.google.co.uk/group/mi...nk=gst&q=peter-t+LoadPicture#9a482ba3a32633b1

For your purposes there's a complete example of formatting a comment with a
picture and sized to original picture size.

Regards,
Peter T

 

[Joergen Bondesen]

Hi Peter



Thanks for your reply on my enquiry.

I find both macros/functions very useful, but with my humble VBA experience
I do think they do not fit to my needs.



I have this spreadsheet with 50 Comment with different pictures. I don't
have the picture-files, so the picture must be embedded in the Comment.
Both your macros needs path for picture-file. I regret that my enquery was
not so descriptive.



I do hope you can help me with my problem.





Best regards

Joergen Bondesen

 

[Peter T]

If the picture has already been embedded in the comment as a picture fill
format I don't know how to get it's original size.

Where did the picture come from in the first place, and how did you apply it
to the comment.

Regards,
Peter T

 

[Joergen Bondesen]

Hi Peter

Where did the picture come from in the first place, and how did you apply
it
to the comment.

I had the pictures in a folder, somewhere.



Applying:

Right-click on the cell
-Insert Comment
-Double-click on the border of the comment
-Choose the Colours and Lines tab
-In the Fill section, click the drop-down for Color
-Choose Fill Effects... | Picture | Select Picture...
-Choose your picture and click Insert
-Click OK




On dialog box 'Format comment' - On 'Size tab' is there a button: 'Reset' I
can use, but 50 Comments takes time.

I you don't come with some magic, I think I will write the size in the
Comments and then read the size and then resize the Comments.

 

[Peter T]

On dialog box 'Format comment' - On 'Size tab' is there a button: 'Reset'

I'm curious, in my version the Reset button is only available with a
Picture. The button is greyed out in the Format/Size dialog when a comment
is selected, even if the comment is formatted with a picture. Are you saying
the reset button is available for you with a Comment. Which Excel version
are you using.

I had the pictures in a folder, somewhere.

You've lost them? You are the second person today !

Regards,
Peter T

 

[Joergen Bondesen]

Hi Peter.

I'm using Excel 2007 (Danish).
I have tryed Excel 2003 (UK) no Button.

Below works (only Test)


Option Explicit

'----------------------------------------------------------
' Procedure : CommentPictureResize
' Date : 20080507
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Resize Picture in Comment.
' Note : One line in Comment: PictureSize: '298*270'
' Fontsize 5. Tested in Excel 2007.
'----------------------------------------------------------
'
Sub CommentPictureResize()
With Range("A1")
.Comment.Shape.Select True

Dim Psize As String
Psize = .Comment.Text
End With

Dim Splitstring As Variant
Splitstring = Split(Psize, "*")

With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Height = Splitstring(0)
.Width = Splitstring(1)
.LockAspectRatio = msoTrue
End With

Range("A1").Select
End Sub