Delete All Shapes In A Range

May 29, 2008

I have been researching how to create a macro (that will attach to a button), that will delete ALL Shapes and Pictures in a range of cells. So far I have only met with limited results. The Range can be hard coded into the script as it will not change. Does anyone have a script written already they would care to post? I am using Excel 2003.

View 9 Replies


ADVERTISEMENT

Delete Shapes On Specified Row

Jan 5, 2008

I have some drop down control boxes in rows that I want to be able to delete using a macro. If highlight the rown and use Edit, Delete, the data is gone but the controls are still there. How can I delete this using a macro?

View 3 Replies View Related

Delete All Shapes On Worksheet

Sep 26, 2007

I've written a macro that plots a graph directly in a white painted sheet, using lines and arrows figures.
The problem is that everytime I plot something, the xls-file gets larger, and the size doesn't change if I delete the figures. Since there's a lot of lines and arrows in my plot, I use 'Select All' and Edit-> Remove and this removes all figures, or at least for the eye. I suspect they are still left there somewhere, since the size grows with every new plot and since that sheet seems to have gotten a bit slower, when moving between cells.

Am I correct, or is there another reason for the size/ speed issue? If I my theory is correct, how do you permanently delete these "invisible" figures?

View 8 Replies View Related

Code To Delete Shapes But Not Charts

Nov 25, 2011

I have the following code:

For Each wShape In ActiveSheet.Shapes
wShape.Delete
Next wShape

This works great to delete the buttons and rectangles I have on my worksheet, but it is also deleting charts. Is there any way to avoid the chart deletion?

View 5 Replies View Related

Delete Shapes On Workbook Deactivation

May 13, 2008

I have a workbook in which certain shape objects (2 command buttons and a combobox) can be present on a worksheet if another macro has been run. When a worksheet is closed and another opened, or the workbook closed, I need the shapes to be cut if they are present. There are other shapes on the sheet which must not be deleted.

Each shape has a defined name which is the worksheet name followed by DD, GetStats and Rfrsh

I have tried the code below but get 'The item with the specified name was not found' error message if the one or more of the shapes are not present. The code works for any of the specified shapes that are present.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Range("f5:i17").ClearContents
If Not Sh.Shapes(Sh.Name & "DD") Is Nothing Then Sh.Shapes(Sh.Name & "DD").Cut
If Not Sh.Shapes(Sh.Name & "GetStats") Is Nothing Then Sh.Shapes(Sh.Name & "GetStats").Cut
If Not Sh.Shapes(Sh.Name & "Rfrsh") Is Nothing Then Sh.Shapes(Sh.Name & "Rfrsh").Cut

End Sub

I am sure I am not using the 'If Not' and 'Is Nothing' properly but can't figure out how it should be.

View 5 Replies View Related

Delete Shapes But Not Active X Controls Box On Same Sheet In VB

Mar 12, 2013

I have an active x control command button on the same sheet as some shapes (lines,circles) which are generated from some parameters. I want to be able to delete the current shapes so I can plot the next shape. Below is the delete shapes part of the code, not sure how to complete the code.

Currently, the box is getting deleted as well as the shapes

Code:

Private Sub CommandButton1_Click()
Dim line1_Beginx As Single, line1_beginy As Single, line1_endx As Single, line1_endy As Single
Dim line2_Beginx As Single, line2_beginy As Single, line2_endx As Single, line2_endy As Single

[Code]....

'Calculate positions of line vertices for plots

line1_Beginx = 1000
line1_beginy = 1000
line1_endx = 1000 + CD_2
line1_endy = 1000

[Code]....

View 2 Replies View Related

Run Time Error 7: Out Of Memory. Selecting Shapes To Delete

May 24, 2005

i have a macro which is executing some cleaning up and then copying information from project sheets to a summary sheet. the individual project sheets are (currently still manually) copied/moved into the big(ger) overview workbook. to support the work with the individual project sheets they contain a macro that is assigned to some (three) shapes. one part of the cleanup is stripping the macros off the individual project sheets:

' remove the traffic-light macros of the project sheets from all sheets in case of unwanted or uncontrolled "infection"
For CurrentWS = 1 To SheetCount
Worksheets(CurrentWS).Select
ActiveSheet.Shapes.SelectAll
Selection.OnAction = ""
Worksheets(CurrentWS). Range("A1").Select
Next CurrentWS

on the line "ActiveSheet.Shapes.SelectAll" i get frequently (not always... ) a run time error '7': out of memory. it happens on at least two computers. i closed all other SW. according to the windows task manager information there should be plenty of memory free.

questions:
- how to get rid of the error message? OR:
- is there another (more smart) way to strip the ("imported") macros, while keeping the "main" marco alive that does not lead to an error message?

View 2 Replies View Related

Delete Buttons Made From Shapes When Copying Sheet To New Workbook?

Jan 27, 2014

I have a code that copies a sheet into a new workbook. The copied sheet is for information only. On the original sheet I have various macro's assigned to buttons made from using Insert Shape command. I assume if I can find away to remove the buttons then the functionality of the macro's would be lost which is what I want?

View 9 Replies View Related

Creating Shapes (Rectangles And Shapes) From Input Cells?

Aug 30, 2013

Im trying to create shapes Rectangles and Squares with different colours within an excel sheet, where the length and height of the shapes is generated by input values.

Also is it possible to create 3D shapes, again where the size of the shap is generated by input cell values.

View 1 Replies View Related

Excel 2007 :: Add Shapes In Range Of Cells To ShapeRange

Feb 3, 2012

Using Excel 2007.

I have shapes in 3 different rnages of cells on a cell worksheet:
R1 = $D$5:$D & lngRows (row=32)
R2 = $G$5:$G & lngRows (row = 51)
R3 = $M$5:$M & lngRows (row = 50)

I need to set the properties of the shapes in each range differently The shapes in R1 are Left + 46 But the shapes in the other 2 ranges need to be just left My problem is in this bit

Code:

'Set properties for each shape in ShapeRange
For Each sh In ws.Shapes

I need to be able to set properties for each range separately instead of the entire sheet. Full code below (only Range $D at the moment - works)

Code:

Option Explicit
Sub AddShape3()
'Purpose: Add small rectangles to database table/fields for
' brainstorming and documenting relationships and queries
'
'Resources:

[Code]....

View 2 Replies View Related

Macro To Copy Shapes From Same Range From Multiple Worksheets To One Worksheet

Sep 14, 2008

I have one master worksheet named "Season" and 30 other worksheets named "1,2,3,4 and so on to 30". I ideally want to copy the shapes (msoShapeOval) from the worksheets- "1-30" to worksheet- "Season". When the shapes (msoShapeOval) are copied from worksheets "1-30"

I want them to keep thier position that they were in when copied to worksheet- "Season".

The shapes (msoShapeOval) are in range "A1:AZ43" in worksheets "1-30" and would be placed in worksheet "Season" range "A1:AZ43"

View 9 Replies View Related

VBA With Shapes

Dec 3, 2008

I have a macro that opens Word pastes data in from Excel,

I want to be able to paste an arrowed line that I have drawn, also if it changes, I want it to reflect that change.

Here's the
Item("Direction1").Range.InsertAfter Worksheets("BSID").Range("Line3").Value

I have my bookmark in Word setup, I'm guessing that instead of a cell ref I need the name of the Line, which is "Line 3" in this case.

I ran a recorded macro and copied and pasted the line and instead of "Range" I got "Shapes" so I assumed that I just needed to change these words but no joy.

View 9 Replies View Related

Working With Shapes

Mar 24, 2009

Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?

View 14 Replies View Related

How To Remove All Shapes

Nov 17, 2009

i have an excel file for using my manufacture planning. i dont know why but there was created too many TextBoxes(but its shapes)

I'm giving my excel file , you can see what i'm talking about at G column and 193. field
if you can solve this problem, i will be pleasure to you

View 10 Replies View Related

Deleting Shapes In VBA

Sep 18, 2012

I have some shapes on a worksheet that need deleting on change of a combo box. Unfortunately I'm unable to use the below code as not all the shapes need deleting, just the ones that are named in a table on another worksheet.

For Each s In ActiveSheet.Shapes
s.Delete
Next s

Also not all the shapes named in the table will be on the worksheet as this depends on what is selected from the combo box.

View 2 Replies View Related

Replacing Shapes With VBA

Aug 30, 2009

I am a not too good programmer, but I construct chemotherapy regimens for my medical department. They are used to minimise the risk of wrong dosing to patients, etc. Every regimen is an .xlt file, and about a third of them (there are about 100) generate, in addition to pharmaceutical prescription, automatic letters to patients (for those regimens where patients administer oral dosing at home).

These patient-letters have the hospital's logo on them (the logo is a "shape").

However, the problem is that another hospital also wants to use my system. So I will have to replace all these logos with the other hospital's. I would like to do this in an automated way. I have received the other hospital's logo also as a shape.

I have already constructed a data-inserting macro in a separate file. This macro systematically opens each xlt file and replaces the text in key locations according to the file the macro is in (geographical names, etc.).

Is it possible to automatically replace the logo-shapes in a similar way, with these additional facts/wishes in mind:

1. There is always only one type of shape (a logo) in the xlt files.

2. Even though the shapes are exactly the same, it is possible the names are different.

3. The exact location for the shape is different in each xlt file, so I'd like the replacement to get the same top left coordinate as the one it replaces.

I have constructed 2 different files with the same data-inserting macro, each containing one of the logos (so I can change back to my own hospital) and geographic data. When the data-inserting macro is used, I'd like it to identify the shape in the data-inserting file (there's only one shape in this file), copy it, then identify the shape(s) in the present xlt file, and then replace these, in the exact same location.

View 9 Replies View Related

Shapes With No Macro

Jul 10, 2006

I have a shape that will have no macro. How do I prevent this shape
from being selected and prevent the message that no macro can be found,
when it is clicked?

With shapes that do have macros how do I disable them ?
How do I keep them visible but not have the mouse cursor change on mouse
over and not have the macro run on a mouse click.

I tried to use Enabled = false but apparently that property cannot be used
for a shape.

I need to toggle between enabled and disabled.

View 9 Replies View Related

Drawing Circles And Other Shapes?

Jun 16, 2013

I want to draw circles from 3 points and other geometrical shapes. The points will be given from clicks on the background picture.

View 1 Replies View Related

Auto Shapes : Areas

Apr 23, 2008

I'm trying to calculate the area of a Freeform shape.

Is this already stored somewhere e.g a shape property?

If not is there a macro available to do this?

View 12 Replies View Related

Create Hyperlinks With Shapes

Feb 9, 2010

This code finds and matches the Text value of a Shape to the value of a cell on seperate worksheets within the same workbook.

View 9 Replies View Related

VBA To Ungroup All Shapes On A Page

Feb 23, 2007

I need code to do this: When I select a range of cells, say E20:H24 then hit a button the code behind the button would ungroup all the shapes in that area. So if I have grouped shapes elsewhere, they would be uneffected.

View 9 Replies View Related

Selecting Group Name For Shapes

Mar 5, 2007

is there a way to select the group of the shape instead of a single shape i've tried .onAction and Application.Caller to do this ..but this is giving me the single shape that i clicked on instead of the whole group.'

View 9 Replies View Related

Can You Disable Right-click On Shapes

Jul 17, 2007

I would like to completely disable the drop down list on the right-click.
I've got

.CommandBars("Cell").Enabled = False

set up and this works on cells but it still leaves it working on shapes.

.CommandBars("Toolbar List").Enabled = False

is similar. Anyone know how to fix this?

View 9 Replies View Related

Change The Colour Of Shapes

Jul 23, 2007

the VBA to change to colour of a shape based upon the value in a cell?

e.g. if cell "a1" = 10 then colour circle red

View 9 Replies View Related

Using Shapes To Hide Zeros

Oct 17, 2007

I want to write a macro that uses shapes to hide zeros; I have seen this done before but don't really understand how to set it up for myself.

On my sheet, there is a list of funds (Cells A4 to A79), a mixture of which will make up any given portfolio. Cell A1 houses a drop down list of portfolios. When I change cell A1, the numbers are automatically updated, as they are pulled from other worksheets in the spreadsheet. I want to be able to have the funds with zeroes next to them be automatically hidden. I believe there is a way to do this using shapes.

View 9 Replies View Related

Define Name For Lines/shapes

Feb 10, 2008

Is there a way to define a name (Insert-->Name-->Define) for lines and/or shapes drawn using the Drawing toolbar?

I'd like to assign a specific name to a rectangle I've drawn outside of the sheet print limits. By using an If-Then formula, I'd like to bring that shape into a specified cell based on the conditions of the If-Then statement.

View 9 Replies View Related

Arrays Of Shapes Specs

Aug 17, 2008

I have a collection of shapes (Rectangles) that are being grouped into a single diagram with the For each shape, index on name, group method. I am having difficulty approaching 2 further issues:

1. before I group them I want to assign the Name, Height and Width to an array so that I can write the array to a worksheet once all the shapes have been identified.

2.Also, the Length needs to be assigned (in some cases it is the Height and in others it is the Width where Length is the larger of Height or Width)to the array for each item. Where is the best place to add the definition of length as it is not relevant to drawing the rectangle but to recording the components.

View 9 Replies View Related

Inserting Pictures Into Shapes

Apr 30, 2009

I have written some code to insert pictures into shapes as part of a facility management problem I have.

Code takes the names from a list of pictures (ConditionList) and places each one into a corresponding shape - therefore populating a report on the facility.

Works to the point of inserting the pictures OK but if I refresh the ConditionList by adding or deleting pictures, my shapes dont refresh.

Sub ConditionPics()

Dim ConditionListCount As Integer
Dim ShapesCount As Integer
Dim ConditionList As Range

On Error Resume Next
Application.EnableEvents = False

View 9 Replies View Related

VBA To Group Worksheet Shapes

Apr 1, 2009

I want to "GROUP" a selection of objects that are put onto a worksheet. But I won't know until the objects are created what the names of each object will be. When I create the objects I will know which objects that I want grouped together.

When I record a macro when I select then "GROUP" said objects I get the following line of code


ActiveSheet.Shapes.Range( Array("FlowCtrl_S2_Decision000011", "FlowCtrl_S2_Decision00001", "FlowCtrl_S2_Decision000012", "FlowCtrl_S2_Decision00001a", "FlowCtrl_S2_Decision00001b")).Select
Selection.ShapeRange.Group.Select

What I need is how to "dynmically" create the Array(....) portion from say a delimited string. Like

"FlowCtrl_S1_Decision00002", "FlowCtrl_S1_Decision000021", "FlowCtrl_S1_Decision000021_1FlowCtrl_S1_Decision00002

In the attached workbook. When you click the button 7 objects will be put onto the worksheet. I want when the routine is complete that the the 7 objects are "GROUP"ed and given a particular name. So that I can point and select at one item and move all 7 at the same time.

View 9 Replies View Related

Add Shapes To UserForm Via Code

Nov 25, 2006

How do i draw shapes on the fly on forms?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved