Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 7th, 2006, 01:30 AM
Registered User
Join Date: Feb 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Clearing Drawing Objects from a given Excel range

Sub wipeoff()
Do While ActiveSheet.Shapes.Count <> 0
End Sub

The above macro clears all pictures/drawing objects from the active sheet.

Now my problem is that I wish to change this macro so that removal of shapes/picture objects is restricted to the user given selection only (not the entire sheet).

Please help.
Thank you in advance

Old September 8th, 2006, 10:07 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

I’ve never tried this, but it seems that
Sub WipeOff()

    Do While ActiveSheet.Selection.Shapes.Count > 0

    ' Or

    Do While Selection.Shapes.Count > 0

End Sub
should do what you need.
Old September 11th, 2006, 12:31 AM
Registered User
Join Date: Feb 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

Thank you, but this does not work, it generates error saying "object does not support this property or method"

Old September 11th, 2006, 01:06 AM
Registered User
Join Date: Feb 2006
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks to Maccas (from MS Office Forum) and BrianWren here for contributing into the settlement of query.

The problem is solved as I made some adjustments in Maccas's macro as follows.

Sub testit()
WipeOffRng Selection
End Sub

Sub WipeOffRng(WipeRange As Range)
Dim isect As Range
Dim wkSheet As Worksheet
Dim Shp As Shape
Dim rngShp As Range

    ' Set the worksheet
    Set wkSheet = WipeRange.Parent

    ' Loop through every shape
    For Each Shp In wkSheet.Shapes

        ' Dtermine the block range
        Set rngShp = Range(Shp.TopLeftCell, Shp.BottomRightCell)

        ' Test for any sort of overlap
        'If Not Intersect(WipeRange, rngShp) Is Nothing Then
        ' Test for fully inside

        Set isect = Intersect(WipeRange, rngShp)
        If isect Is Nothing Then


        End If

    Next Shp

End Sub

Similar Threads
Thread Thread Starter Forum Replies Last Post
clearing unused symbols and objects nairaby Flash (all versions) 4 August 16th, 2007 02:41 PM
Importing a Range from Excel JezLisle Access VBA 0 July 27th, 2007 02:54 AM
Method Range - VBA - Excel teatimer Beginning VB 6 0 May 22nd, 2006 03:50 AM
Accessing Drawing Objects from code isuru Excel VBA 0 August 29th, 2005 11:45 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.