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
    ActiveSheet.Shapes(1).Delete
Loop
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
Default

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

    Do While ActiveSheet.Selection.Shapes.Count > 0
        ActiveSheet.Selection.Shapes(1).Delete
    Loop

    ' Or

    Do While Selection.Shapes.Count > 0
        Selection.Shapes(1).Delete
    Loop

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
Default

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
Default

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

            Else
            Shp.Delete

        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.