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


ADVERTISEMENT

Hide A UserForm On Workbook Deactivation

Nov 16, 2009

I often have several workbooks open and shift from one to another. One of these contains a floating UserForm; and the trouble is that when I leave this workbook and shift to another one, the UserForm belonging to the first remains open and visible in the new workbook; and I cannot hide this nuisance. Within the same workbook, it’s easy to hide the Useform when shifting to another worksheet (through code for Private Sub Worksheet_Deactivate()). Is there something similar for when the whole workbook is deactivated?

View 4 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

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 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 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 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

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

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.

Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")

[code]....

View 2 Replies View Related

Delete Sheets And Create New Workbook In Shared Workbook

Jun 2, 2014

I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.

The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?

View 3 Replies View Related

Disabled "Delete" For One Workbook Turned Out To Be Applied To Every Workbook...

Jul 13, 2009

I wanted to disable the function "Delete" when right-clicking on worksheets.

I copy paste the following VBA code in every sheet code :

View 10 Replies View Related

VBA - Delete Workbook: Can It Be Done?

Oct 3, 2008

I have a workbook called wb1 stored in folder C:folder1.

I open the workbook and make some changes to it.

At that point I want to have a macro that will save the workbook with all the changes made in folder C:folder2, under the new name wb2, and somehow delete wb1 from C:folder1.

View 9 Replies View Related

Delete New Workbook

Apr 20, 2007

I have a problem deleteing a new workbook that has been .Add[ed].

When saving the active workbook as a new workbook the user is prompted for a file name via a userform.

If the new file name is the same as an existing workbook the error handle restarts process ,adding another new workbook.

or if the project number is unique it is saved ...

View 3 Replies View Related

Delete Workbook After Two Weeks

May 17, 2009

Is it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?

Or does it have to be active to be able to delete itself.

I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.

I do not want others to use my file (of course I'll back it up before I'll go).

View 7 Replies View Related

Delete All Macros In Workbook?

Jan 4, 2011

I want to delete all macros from my workbook (so it will work well in Mac 2008). I went to Alt-F11 and deleted all Macros there and check every sheet and found no references. I did all that and when I try to open it in Excel for Mac it says there are still Macros.

How can I ensure my workbook has no macros?

View 9 Replies View Related

Delete Worksheet With VBA From Different Workbook?

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

Delete Workbook After 3 Attempts

Jan 24, 2008

Now that the code is configured to do what I want, can someone assist me in adding a code that will delete the expired file after the third attempt of the user trying to open an expired file.

Private Sub Workbook_Open()
Dim StartTime#, CurrentTime#

Const TrialPeriod# = 1 '< 1 days trial

Const ObscurePath$ = "C:"
Const ObscureFile$ = "LBFileLog.Log"
'*****************************************

If Dir(ObscurePath & ObscureFile) = Empty Then
StartTime = Format(Now, "#0.#########0")
Open ObscurePath & ObscureFile For Output As #1
Print #1, StartTime....................................

View 10 Replies View Related

Delete Active Workbook

Oct 10, 2004

if it is possible to delete the current workbook that is open?

I'm thinking of using an add-in to delete the workbook i'm viewing

View 9 Replies View Related

Delete All Names In A Workbook

Aug 13, 2006

Delete All Names in a workbook. Maybe delete names one time?

View 2 Replies View Related

Delete Sheets In Active Workbook

Nov 27, 2008

I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?

View 2 Replies View Related

How To Delete All Defined Names From A Workbook

Nov 11, 2005

How to delete all defined names from a workbook

View 14 Replies View Related

Delete A Range When Workbook Is Saved

Nov 15, 2011

I currently have the code below that closes all but 1 sheet in my workbook called Main

Is there anyway i can clear a range of cells on a couple of sheets within the workbook when save is pressed?

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For a = 1 To Sheets.Count
If Sheets(a).Name "Main" Then Sheets(a).Visible = 2
Next a
End Sub

View 4 Replies View Related

When Open Workbook Delete Contents

Feb 20, 2009

ive tried creating a macro/code that when you open a certain workbook it automatically deletes the contents. you see i have a workbook containing 100 sheets that i need to update each week but i have to delete all the contents first, is there a quick way to do this when i open the workbook up?

View 9 Replies View Related

Macro To Delete Sheets In Another Workbook

Mar 2, 2009

my code to loop through sheets in another workbook does not want to work. in This Workbook (SourceWb), I copy the current workbook using SaveCopyAs and then reopen the new workbook. from ThisWorkbook (SourceWb), I am trying to delete any sheet between "First" and "Last" where a specific criteria is not met. so starting from the For Each to get my loop values, where PMs is a list of people's initials:

For Each c In Range("PMs")

If c.Value = "" Then GoTo AfterArrays

View 9 Replies View Related

Delete Original Workbook After Save As

Mar 21, 2008

I don't believe this is possible but I've been wrong before.. Is it at all possible to Kill the current workbook using VBA? I " saved as" in another location and do not wish to keep the current workbook.

View 3 Replies View Related

Delete A Workbook If Opened On Another Machine

Apr 15, 2008

Is this possible to set a macro which will delete the file if opened on another computer? Ie say on WB open I want to set a macro that if Environ(“Username”) = “ABCD” then proceed else delete the file permanently from th e other computers machine. If deletion of file is not possible then deletion of sheets or contents of sheets should be deleted. The sheets will be protected. Expecting a solution to delete the file.

View 2 Replies View Related

Delete Special Cells And Protected Workbook

Mar 28, 2014

I have a code which deletes all contents of cell in range except formulas, from sheet 2 to 13. Code works, but I have included toggle button on each sheet to lock/unlock sheets.

If I lock sheets, code doesn't erase cells as needed.

I have figured out code that works for me, but looks like It's not performing quite correct. Although code executes quite quick...

This is my current code :

[Code] .....

How could I rearrange this code to work in proper order ?

View 4 Replies View Related

VBA - Delete Sheets In Active Workbook According To Combobox Value

May 8, 2014

That particular question has been solved, but now i need it to work with multiple values from combobox.

So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7

The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"

WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.

Run-time error '-2147417848 (80010108)': Automation error

The object invoked has disconnected from its clients.

When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate

View 5 Replies View Related







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