Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Delete Worksheets with a Background

  1. #1
    Board Regular Gary's Student's Avatar
    Join Date
    Aug 2012
    Location
    Lakehurst, NJ, USA
    Posts
    1,012
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete Worksheets with a Background

    I have a set of workbooks each of which contain many worksheets. I need to examine each sheet and if it contains an applied Background, delete that sheet.

    This is easy to do manually, but very tedious. Can it be accomplished with a macro?
    Have a Great Day!

  2. #2
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Worksheets with a Background

    Quote Originally Posted by Gary's Student View Post
    This is easy to do manually, but very tedious.
    What does the macro recorder give you? I mean, with almost 1,000 posts here I bet you can use the macro recorder and inspect the coding
    For instance, record a macro when you apply the background to a sheet, then delete the background again, and stop the recorder.
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,447
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Delete Worksheets with a Background

    It doesn't appear that it is exposed in the object model. You can set it, but not read it.

  4. #4
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Worksheets with a Background

    Then it will be a manual job. Or SendKeys
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  5. #5
    Board Regular Teeroy's Avatar
    Join Date
    Nov 2012
    Location
    Sydney, Australia
    Posts
    2,240
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Worksheets with a Background

    There is no error produced if you try to remove a background that isn't there so just loop through all the sheets e.g.

    Code:
    Sub Remove_Background()
    For Each Sht In Sheets
        Sht.SetBackgroundPicture Filename:=""
    Next
    End Sub

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,447
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Delete Worksheets with a Background

    Use SendKeys for what?

  7. #7
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Worksheets with a Background

    Stephen: to automate the job, which is now a manual tedious job.
    Unless I misunderstood the question from the OP.
    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  8. #8
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,447
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Delete Worksheets with a Background

    @Teeroy: If I understand the original question, the problem is not how to remove the background, but to detect whether the sheet has one.

  9. #9
    Board Regular Teeroy's Avatar
    Join Date
    Nov 2012
    Location
    Sydney, Australia
    Posts
    2,240
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Worksheets with a Background

    Quote Originally Posted by shg View Post
    @Teeroy: If I understand the original question, the problem is not how to remove the background, but to detect whether the sheet has one.
    @Shg, you are correct. I missed one word in the first post and ....

    You can find out if a sheet has a background image by checking the workbook ZIP container to check for a sheet relationships element (e.g. sheet1.xml.rels) but you'd need to programmatically access the internals of the zip container which I've never done; I've only gone in manually to recover information. You probably also need to walk the XML tree to see whether it contains a reference to an image as well.
    Last edited by Teeroy; Nov 14th, 2014 at 04:09 PM. Reason: Added information

  10. #10
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,447
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Delete Worksheets with a Background

    Wow, great thinking!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •