Shazam
07-13-2006, 06:38 AM
Hi everyone :hi:
I have this code below, it will insert a comment picture when double clicking the cell in column C. In column B has the part# and column C has the formula that reference file path.
="Q:\Reference Photos\Part#\"&B3&".jpg"
My question is do I need that column C?
Is it possible that I could just double click the cell in column B that has the part# and the picture will appear. How can the code be modified to do that?
I would prefer not to use that helper column C.
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 3 Then ' only respond to doubleclicks in column C
Cancel = True ' No need to edit the value in the cell so cancel that
pix Target
End If
End Sub
Sub pix(rngCell As Range)
Dim curWks As Worksheet
Dim myCell As Range
Dim c As Object
Set curWks = ActiveSheet
curWks.Columns("C").ClearComments
If Trim(rngCell.Value) = "" Then
'do nothing
ElseIf Dir(CStr(rngCell.Value)) = "" Then
'picture not there!
MsgBox rngCell.Value & " doesn't exist!"
Else
With rngCell.Offset(0, 0) 'Option 17 columns to the right of A (C)
.AddComment("").Shape.Fill.UserPicture (rngCell.Value)
For Each c In ActiveSheet.Comments
c.Shape.Width = 400
c.Shape.Height = 300
Next c
End With
End If
End Sub
I have this code below, it will insert a comment picture when double clicking the cell in column C. In column B has the part# and column C has the formula that reference file path.
="Q:\Reference Photos\Part#\"&B3&".jpg"
My question is do I need that column C?
Is it possible that I could just double click the cell in column B that has the part# and the picture will appear. How can the code be modified to do that?
I would prefer not to use that helper column C.
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 3 Then ' only respond to doubleclicks in column C
Cancel = True ' No need to edit the value in the cell so cancel that
pix Target
End If
End Sub
Sub pix(rngCell As Range)
Dim curWks As Worksheet
Dim myCell As Range
Dim c As Object
Set curWks = ActiveSheet
curWks.Columns("C").ClearComments
If Trim(rngCell.Value) = "" Then
'do nothing
ElseIf Dir(CStr(rngCell.Value)) = "" Then
'picture not there!
MsgBox rngCell.Value & " doesn't exist!"
Else
With rngCell.Offset(0, 0) 'Option 17 columns to the right of A (C)
.AddComment("").Shape.Fill.UserPicture (rngCell.Value)
For Each c In ActiveSheet.Comments
c.Shape.Width = 400
c.Shape.Height = 300
Next c
End With
End If
End Sub