How can I extract a fill pattern from a cell comments field

mzgaljic

New Member
Joined
Jun 21, 2008
Messages
5
I have a file where comments are used for some cells. The user does not insert any text into these comments fields, but uses the comments feature to display pictures.
A picture is used for the fill pattern of the cell (inserted through fill effects) and that picture is what is displayed when someone moves the mouse over the cell.
The "fill pattern picture" becomes part of the excel file and my question is how can I extract this picture and use it somewhere else.
I can send you the file if needed.
Thanks.
 
Last edited:
Just opened Outlook and saw your mail. I noticed that the results are better if you remove the border line and shadow. Replace the procedure above with this one that removes and resets several properties...

Code:
Sub SaveCommentPicturesToFile(CommentsInThisRange As Range)
    Dim r As Range, c As Comment, LineState As MsoTriState, ShadowState As MsoTriState
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    MkDir ThisWorkbook.Path & Application.PathSeparator & "CommentPics"
    On Error GoTo 0
    
    ChDir ThisWorkbook.Path & Application.PathSeparator & "CommentPics"
    
    For Each r In CommentsInThisRange
        Set c = r.Comment
        If Not c Is Nothing Then
            c.Visible = True
            LineState = c.Shape.Line.Visible
            ShadowState = c.Shape.Shadow.Visible
            c.Shape.Line.Visible = msoFalse
            c.Shape.Shadow.Visible = msoFalse
            c.Shape.CopyPicture xlScreen, xlBitmap
            c.Shape.Line.Visible = LineState
            c.Shape.Shadow.Visible = ShadowState
            c.Visible = False
            SavePicture PicFromClip, "CPic_" & r.Address(0, 0) & ".bmp."
        End If
    Next
    
End Sub
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Tom!
Thanks for joining this one, I was curious how to do it.
Great!!

I have two questions.
1.
Why is there a dot at the end?
SavePicture PicFromClip, "CPic_" & r.Address(0, 0) & ".bmp."
Seems like it makes no difference if it is there or not.
2.
Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?

kind regards,
Erik
 
Upvote 0
Hi Eric. The period is a typo. It is a bitmap. The only choices available are meta and bitmap. If you change the extension, whatever program opens it would likely resolve it as an image, but simply changing the extension does not saveas a jpg.
 
Upvote 0
Thank you for your response, Tom

The typo didn't make any trouble.
It is still not clear to me: when I change the extension I get a jpg file, at least it looks like one.
The icon is jpg, the extension is jpg.
Is that misleading me?

best regards,
Erik
 
Upvote 0
If you change it to bin, you will get a binary file. If you change it to txt, you will get a text file. You're thinking too hard! :)
 
Upvote 0
I might be thinking to hard, but now the confusion is complete.

If you change the extension, whatever program opens it would likely resolve it as an image, but simply changing the extension does not saveas a jpg.<!-- / message --><!-- sig -->
If you change it to bin, you will get a binary file. If you change it to txt, you will get a text file.
those two quotes seems contradictory to me

perhaps the misunderstanding was here
Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?
I meant replacing the "bmp" WITHIN THE CODE.

Still patient with me ? :biggrin:
 
Upvote 0
Replacing "bmp" by "jpg" still works, but there was no difference in filesize, though I expected to find a difference. Any comments?

If you are expecting a format transformation, It ain't gonna happen by simply changing the extension. Rather in code or in explorer...
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top