Adding worksheet picture to a comment

[craig.penny]

Hello all you Excel nerds!

I'm trying to insert a picture into a cell comment. I've found plenty of threads but they are all for situations where the picture is not already within the worksheet or workbook. This is what I've been trying with all different things in place of the "???????" but I haven't figured it out.

Sub NamePic()
      Dim Pic As Object
      Dim Nm As String: Nm = "Picture 2"
      Dim s As Worksheet: Set s = ActiveSheet
      
      On Error Resume Next
      
      s.Cells(1, 2).Select
      Set Pic = s.Pictures(Nm)
      
      s.Cells(1, 5).AddComment
      s.Cells(1, 5).Comment.Shape.Height = Pic.Height
      s.Cells(1, 5).Comment.Shape.Width = Pic.Width
      s.Cells(1, 5).Comment.Shape.Fill.UserPicture   ????????
      
End Sub

"Picture 2" is definitely the right name but now what?

If you read this and you don't know the answer but just have an idea I'd love to hear it!

Thanks in advance!

 

[Greg Truby]

Um - well, I'd say if you have some doubts about your ability to answer, you can sort of write down your answer on your own and subscribe to the thread and see if you would have gotten it right. The nice thing about formula problems is that you can test them before you post them. Do be aware that - at least during high-traffic periods - sometimes an unanswered post is unanswered for a reason. Either the OP has poorly explained the requirements; or he is asking for a solution to an extremely complex or arcane problem that only a small percentage of members would know how to answer (say like how to add a worksheet picture to a comment using VBA ). I'm not saying not to take a look at unanswered posts. I'm just saying don't be discouraged if your batting average is a bit lower on unanswered posts than you'd like.

 

[craig.penny]

or he is asking for a solution to an extremely complex or arcane problem that only a small percentage of members would know how to answer (say like how to add a worksheet picture to a comment using VBA ).

Ha! (and point taken)

Thanks for taking the time to help me. I really appreciate it!

 

[craig.penny]

I just received "Visual Basic Programmer's Guide to the Win32 API" and it's copyrighted 1999. Is that too out of date?

Thanks

 

[Jaafar Tribak]

I just received "Visual Basic Programmer's Guide to the Win32 API" and it's copyrighted 1999. Is that too out of date?

Thanks

I believe the last release of classic VB (VB6) was in 1998 so it is not out of date and the principles for using the Win32 API remain the same even in 64 bit OS.

One thing worth noting is that working with the API in VBA/Office applications is more difficult than in VB6 mainly due to the fact that most objects within Office applications don't have a HWND or a HDC which are often needed in API functions.Also VBA is not compiled code making techniques such as Subclassing/Hooking all the more difficult.

Having said that,if you do carry on and get used to using the Win32 in your VBA you will harvest generously

 

[craig.penny]

That's great

Thanks for responding Jaafar!

Have you MVPs ever considered having a thread along the lines of "MVP suggested reading"?