Hi,
I have some VBA to take a path from the 2nd column and show an image for the cell that has been clicked using the comment box to display the image
It looks great, hopefully others can use it if this error gets cleared up
Example sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]ML053[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]http://media.4rgos.it/i/Argos/9016710_R_Z001A?$Web$&$DefaultPDP570$[/TD]
[/TR]
[TR]
[TD]ML089[/TD]
[TD]https://image.freepik.com/free-vector/smile-background-in-yellow-tones_23-2147637286.jpg[/TD]
[/TR]
[TR]
[TD]ML100[/TD]
[TD]https://www.free-funny-jokes.com/funny-pictures/what-do-you-call-a-sheep-covered-in-chocolate-joke.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have it working and looks great but after around 7 clicks I get RUN TIME ERROR 7 'Out of Memory'
I'm using a tiny sheet as a test only put around 12 lines into it so the size is 20KB
This is my code and the out of memory stops on .Comment.Shape.Fill.UserPicture TheFile
How can I keep the memory down, is it the code or an excel bug?
Just paste into the sheet you are using as it activates on the cell being selected
Thanks for your help
I have some VBA to take a path from the 2nd column and show an image for the cell that has been clicked using the comment box to display the image
It looks great, hopefully others can use it if this error gets cleared up
Example sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]ML053[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]http://media.4rgos.it/i/Argos/9016710_R_Z001A?$Web$&$DefaultPDP570$[/TD]
[/TR]
[TR]
[TD]ML089[/TD]
[TD]https://image.freepik.com/free-vector/smile-background-in-yellow-tones_23-2147637286.jpg[/TD]
[/TR]
[TR]
[TD]ML100[/TD]
[TD]https://www.free-funny-jokes.com/funny-pictures/what-do-you-call-a-sheep-covered-in-chocolate-joke.jpg[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have it working and looks great but after around 7 clicks I get RUN TIME ERROR 7 'Out of Memory'
I'm using a tiny sheet as a test only put around 12 lines into it so the size is 20KB
This is my code and the out of memory stops on .Comment.Shape.Fill.UserPicture TheFile
How can I keep the memory down, is it the code or an excel bug?
Just paste into the sheet you are using as it activates on the cell being selected
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim listWS As Worksheet
Dim targetCol, targetRow As Long
Dim TheFile As String
If Target.Column = 2 Then
If Target.Cells.Count = 1 Then
If IsEmpty(Target.Cells) = False Then
If Cells(Target.Row, 2).Comment Is Nothing Then
' Set listWS = Application.ThisWorkbook.ActiveSheet
targetCol = Target.Column
targetRow = Target.Row
' TheFile = listWS.Cells(targetRow, targetCol).Value
TheFile = Cells(targetRow, targetCol).Value
'With listWS.Range(listWS.Cells(targetRow, 4), listWS.Cells(targetRow, 4))
With Range(Cells(targetRow, 2), Cells(targetRow, 2))
.AddComment
.Comment.Visible = True
.Comment.Shape.Fill.UserPicture TheFile
.Comment.Shape.ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 1.5, msoFalse, msoScaleFromTopLeft
End With
' Set listWS = Nothing
Else
Cells(Target.Row, 2).Comment.Delete
End If
End If
End If
End If
End Sub
Thanks for your help
Last edited by a moderator: