Picture?

[DougDR]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0mm 5.4pt 0mm 5.4pt; mso-para-margin:0mm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Dear Mike/ or anyone

Thank you for your support. I would like to ask a number of questions but for now just one.
Your trick 42 shows how to put pictures into the comment of a cell. It was based on a single cell.
I have a need to have it happen row by row based on a part number which is also the Folder name in which the pictures are placed and based on the picture name, both of these are in the row in question. The picture needs to appear in the comment of the description cell in the same row. There for in a row you have the part number ##-##-NNNN and the picture name @@@@@.jpg, the folder where the picture is E:\My Documents\####\######\##-##-NNNN

Hope you can help

 

[DougDR]

Hi again
Apart from being able to set the size of the comment widow I will also need to set the "Lock picture aspect ratio" and "Rotate fill effect with shape"

I relly hope that someone can help

 

[SuperFerret]

Hi DougDR,

Just a thought, have you tried recording inserting a picture and selecting the "Lock picture aspect ratio" and "Rotate fill effect with shape" (possibly on a blank workbook) and seeing what the macro recorder throws out?

If you post that here, or perhaps in a new thread, someone may be able to help you add in the new code

Also don't forget to use Code Tags when posting to the board, it makes it easier to read

 

[DougDR]

Hi SuperFerret
Thanks will try that

Sorry what are "Code Tags"

 

[SuperFerret]

Hi SuperFerret
Thanks will try that

Sorry what are "Code Tags"

Code Tags are what people use on the board to retain spacing and indents on code, it makes it easier when trying to find errors.

Basically there are a couple of ways to use them, either you can type
[code#] then paste your code here [/code#] (removing the #'s)

Or paste your code into your reply, highlight it, then hit the button that has the # symbol on it.

It turns this:

Sub Add_Comments()
'All the coding stuff
End Sub

into:

Sub Add_Comments()
    'All the coding stuff
End Sub

 

[DougDR]

Thanks I will do it that way from now on. You have been very helpful: thank you so much. What do you do that allows you to be so good with Excel and VBA?

 

[DougDR]

Hi again

I have tried what you said and the following is what happened. Do now what to do with it ...........????

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 2011/06/15 by DOUG DAVEY
'

'
    Range("F10").AddComment
    Range("F10").Comment.Visible = False
    Range("F10").Comment.Text Text:=""
    With Selection.Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.BackColor.SchemeColor = 80
    Selection.ShapeRange.Fill.UserPicture "E:\My Documents\My Pictures\Globe.jpg"
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 141.75
    Selection.ShapeRange.Width = 141.75
    Selection.ShapeRange.ScaleHeight 1.39, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.11, msoFalse, msoScaleFromTopLeft
End Sub

 

[SuperFerret]

Yhanks I will do it that way from now on. You have been very helpful: thank you so much. What do you do that allows you to be so good with Excel and VBA?

I wouldn't say I'm good, especially compared to some of the people on here!

I just use Excel every day and of course come on here to offer help when I can. It's a great place to learn, and you'll find there are lots of ways to acheive the same result with Excel.
With VBA, I tend to record a lot of repetitive tasks to see if it's possible to speed them up, and I lurk around here looking for any macro's that sound interesting and then try to pick them apart to understand them better!

If you make yourself a regular visitor here you can learn a lot! That's all I did!

 

[SuperFerret]

Ok, it'll be these sections we need:

Selection.ShapeRange.Fill.UserPicture "E:\My Documents\My Pictures\Globe.jpg"
    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 141.75
    Selection.ShapeRange.Width = 141.75
    Selection.ShapeRange.ScaleHeight 1.39, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.11, msoFalse, msoScaleFromTopLeft

It's just whereabouts to add them into the code, I'll have a play

 

[DougDR]

You my dear sir/lady are a star. [grammer?] Thank you so much