add picture

[willemeulen]

Just learning bit by bit so bear a bit with me picking your brains. There are a few things I would like to modify on the code below but I just do baby steps in order to understand the damn thing.

The code below works perfectly for adding a picture into the comment box depending on the value. But I would like to get it working on cells which already have a comment. I tried to add clear comment but doesn't want to work.

Sub AddPic()
For Each cell In Selection
Pic = "C:\SCimage\shape" & cell.Value & ".jpg"
With cell.AddComment
.Shape.Fill.UserPicture Pic
.Shape.Height = 175
.Shape.Width = 300
End With
Next cell
End Sub


W

[Jan Karel Pieterse]

Adding this just before the addcomment line seems to help:

On Error Resume Next
cell.ClearComments
On Error GoTo 0

[willemeulen]

Thanks Jan Karel, it works perfectly!

Now the next step.
At the start of the macro it refers to the cells selected
How do I set it to a fixed range?
Secondly how do I trigger the macro when it's updated.

Your name rings a bell, have been looking for VBA in Excel for Dummies to get properly started, not found yet but am shore I'll find it. Did you contribute to this book Jan Karel? Although working in South Africa I'm Dutch as well.

Cheers,

Willem van der Meulen

[Bob Phillips]

Try this worksheet change code

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "H5:H10" '<--- change t suit

Application.EnableEvents = False
On Error GoTo ws_exit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Pic = "C:\SCimage\shape" & cell.Value & ".jpg"
On Error Resume Next
Target.ClearComments
On Error GoTo 0
With Target.AddComment

.Shape.Fill.UserPicture Pic
.Shape.Height = 175
.Shape.Width = 300
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code