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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
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
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
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
Jun 28, 2007
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues
One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.
There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.
ive done basic c++ programing years ago, but know nothing about vba or excel macros.
View 13 Replies
View Related
May 11, 2004
I have a workbook with 3 worksheets. I am trying to delete 2 of the three but "Delete Sheet" is grayed out in both the "Edit" menu and RtMouseClick menu. The sheets are not protected and the workbook is shared. Do I have to un-share the workbook in order to delete sheete? If so.
View 9 Replies
View Related
Jan 23, 2009
Need to delete the line on this worksheet and post how they did it.
View 2 Replies
View Related
Nov 26, 2013
I'm trying to delete the buttons on the top of this worksheet, and for some reason am not able to do so.
View 3 Replies
View Related
Mar 3, 2014
I was trying to print a workbook which was about perhaps 20-30 sheets of roughly one page of printed info each. However, I saw the print job and it said it was printing over 7,000 pages!!!
It turns out there were about 4-5 worksheets which were blank (and also didn't have gridlines even though gridlines were checked on in options).
When I switched the sheets to page break view, lo and behold each sheet contained a few thousands "pages". I simply can't seem to reduce it down to a relatively normal size.
I tried going right past the edges of page 1 and deleting all columns/rows to the right and bottom of it to no avail. I selected the whole worksheet and removed all borders and any cell content with no luck.
I saw other "solutions" on the internet about copy/pasting the other unaffected sheets to a new workbook and deleting, but this seems cumbersome.
View 1 Replies
View Related
Apr 7, 2014
I am tring to make a macro that will find all charts in a worksheet and delete them.
the macro is essentilally a monthly refresh that cleans out a template of charts that were pput in the previous month. As the chart names and the number of charts might vary from month to month I just want simple code to select all charts in the worksheet and delete them.
View 4 Replies
View Related
Jan 21, 2009
I am absolutely new to visual basic thing but have a good idea of what I am doing in excel. I have seen some code to delete pics but can't figure out what it is that I am suppose to do. What I am doing is coping and pasting something from the internet. I do not need any of the pics that are pasted into the worksheet and am tired of deleting them individualy.
View 5 Replies
View Related
Aug 7, 2009
An old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Need figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.
Try pasting the following two event procedures into the Help sheet module:
'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub................
View 4 Replies
View Related
Jan 19, 2010
Can someone help me out with this script?.
I keep getting "Subscript Out of Range Error"
I'm trying to delete all the code on sheets(1) of the active workbook.
View 8 Replies
View Related
Feb 22, 2010
I have recorded a macro to copy a worksheet, perform some operations on the copy then delete the copied worksheet. My problem is that when running the macro I get a dialog box asking me if I am sure that I want to delete the sheet and I need to press OK. How can I get the macro to say it is OK to delete the sheet?
The last line of my macro is ActiveWindow.SelectedSheets.Delete. I have tried things like:- ActiveWindow.SelectedSheets.Delete = TRUE
View 2 Replies
View Related
Nov 3, 2011
It's simple enough to delete all macros. I have a form that starts as a landing area for a bunch of data then it gets renamed. I want to delete both of my command buttons from the sheet and remove all of my macros. How am I going to pull this off, how can i save it to the sheet the first time?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'cut command buttons
ActiveSheet.Shapes("CommandButton1").Select
Selection.Cut
ActiveSheet.Shapes("CommandButton2").Select
Selection.Cut
[Code] .........
View 1 Replies
View Related
Dec 9, 2011
I am running VBA that will open a new workbook, at the end of that VBA code I have a line of code that reads:
Code:
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
When the code hits those lines it throws a VBA error 400. What do I need to do to have Sheet1 deleted from the "new" workbook?
View 2 Replies
View Related
Oct 22, 2008
I have exported a report out of Quickbooks with all my customers that have done business with us from 2005-2008. I want to be able to delete the customers from the worksheet that have done any business in 2008. So that I am left with a list of customers that have not done any business with us in 2008. Can a formula or macro do this for me. I don't just want to delete the rows that contain any dates in 2008, but delete the customer as a whole from the workbook.
TypeDateNumMemoNameItem@Home RealtyInvoice10/20/200522813BIC Clic Stic Black Barrel Red Trim White and Red Imprint Black Ink Medium Point@Home RealtyCSInvoice10/20/200522813Printing an additional color on the merchandise@Home RealtyADDCOLORInvoice10/20/200522813Shipping & Handling@Home RealtyShippingInvoice12/01/2005FC 2058Finance Charges on Overdue Balance@Home RealtyFin ChgInvoice07/18/200730753Silkscreen add web address to shirts@Home RealtySilkscreenInvoice07/18/200730753Silkscreening Set-up fee@Home RealtySet-upTotal @Home Realty1 Source ManufacturingInvoice08/16/2007311575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice08/16/200731157Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice08/16/200731157UPS Ground Shipping & Handling1 Source ManufacturingShippingInvoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/200731875Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice10/25/200731875Shipping & Handling1 Source ManufacturingShippingInvoice07/08/2008343575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice07/08/200834357Shipping & Handling1 Source ManufacturingShippingTotal 1 Source Manufacturing
View 9 Replies
View Related
Apr 22, 2006
I've included bits of my code and some debug output. Whenever I attempt to add a worksheet, either BEFORE or AFTER I get the following ERROR MESSAGE: Method 'Add' of object 'Sheets' Failed. if I use the .add without before and after a worksheet is added. The problem is that it is always added before the active sheet. I need to replace a single sheet in the correct position of possible 10 sheets. I know the names of the sheets and which one I want to replace, but I can't get EXCEL to move sheets, add sheets by position Number or name, without getting the ERROR.
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public xlRange As Excel.Range
.....
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
....
xlBook.Worksheets.Add After:="Accounts Receivable" *****
?xlsheet.Name
Accounts Receivable
?xlbook.Worksheets.Count
4
?xlbook.Worksheets(4).name
Accounts Receivable
View 2 Replies
View Related
Mar 1, 2007
Does anyone have SIMPLE code for this that can be run in a normal Sub. My worksheet will always be named "Cleaned". I have seen some posts regarding Functions and other things that seem more complicated than necessary.
View 9 Replies
View Related
Apr 22, 2008
On my Worksheet, I have a Commandbotton that executes this macro to delete all comboboxes in another sheet.
Sub DeleteShapes()
Dim obj As OLEObject
For Each obj In Sheets("Sheet").OLEObjects
If TypeOf obj.Object Is MSForms. ComboBox Then
obj.Delete
End If
Next
End Sub
This method works most of the time, but sometimes I get this error:
delete method of OLEObject class failed. I red in this forum, that I should set the TakeFocusOnClick Property of the CommandButton to false.
View 3 Replies
View Related
Jun 5, 2006
How do I delete a command button I've entered on my Worksheetright-clicking does nothing;control-key+right-click does nothing alt+ctl+right-clicking does nothingWhat's the secret?
View 8 Replies
View Related
Jul 11, 2014
I want to delete a cell row in a table when I delete a corresponding worksheet with the same name as the cell.
For example, on the attached, if I delete the worksheet "Shawn" then I want row with "Shawn" deleted from the table on the "Example" tab. If I delete "Becky" worksheet then the row with "Becky" should be deleted from the table on the "Example" tab.
Delete cell when worksheet deleted.xlsx
View 4 Replies
View Related
Aug 1, 2014
I have a short subroutine to delete a row in a second worksheet (SUMMARY EXPENSES) if one is deleted in the main worksheet (AS CODES) as per below:
[Code] .......
In theory the row deletion should only be triggered IF dynamiccounter is smaller than staticcounter, but the fact is anything whatsoever triggers the row deletion. If I type text into a cell a row gets deleted. If I copy a cell, a row gets deleted. It works great at deleting the line, but I suspect it has nothing to do with the counters I set up and the reassigning of the counter value towards the end of the If statement.
View 4 Replies
View Related