(worksheet).delete method causes Project Reset !!

stevebovy

New Member
Joined
Jun 16, 2008
Messages
18
Hi,

I have encountered what seems like a very frustrating and stupid Excel vba bug.

When I use the worksheet.delete method. My vba project is RESET.

All forms object classes and variables disapear into the BIT BUCKET.

Does any one have any ideas on what is causing, this, and wehter there
is a way to avoid it or work around it. ??
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What is the exact code? Are you using codename.delete or Worksheets("tab name").Delete?
 

stevebovy

New Member
Joined
Jun 16, 2008
Messages
18
Thanks for responding :)

either of the following:
*************************
dim wksheet as worksheet

set wksheet = activesheet

wksheet.delete
*************************
Or
************************
for each xx in worksheets

if some-condition
xx.delete
end if

next
**********************

If I use "sendkey" there is no problem, but then you get
the ugly unsightly menu-flashes which application-screen-no-update
does not effect
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Which version of Excel? Do you have any ActiveX controls or OLEObjects on the sheet? (I assume you're not running code in the sheet at the time?)
 

stevebovy

New Member
Joined
Jun 16, 2008
Messages
18
I have been developing on office 2000, and testing
on 2000, 2003 and 2007

I have one activeX button, and I used to have a button-click handler,
but I removed the code.
 

stevebovy

New Member
Joined
Jun 16, 2008
Messages
18
Your recent reply about activex controls was a good hint !!!

It looks like that is what causes the problem,

But is there a solution ???????

I tried deleting the object before deleting the sheet, but that causes
the same problem

Is there some way to delete an imbedded object from a sheet
without reseting the project ?????
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you don't need an ActiveX control, use a Forms toolbar version instead.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
... My vba project is RESET.

All forms object classes and variables disapear into the BIT BUCKET.
Hi,

What do you mean by "reset"?
What do you mean by "disapear into the BIT BUCKET"?

Do you mean that some parts of your project are deleted?
Of course the sheetmodule will be deleted along with the sheet where it belongs to.

kind regards,
Erik
 

stevebovy

New Member
Joined
Jun 16, 2008
Messages
18
What I mean by reset is the memory for all global variables and classes including active form classes is removed. In fact the sheet.delete is being
performed within the button click of a button toolbar within a form, after
the function goes out of scope, the form is unloaded
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,659
Hi,

Rorya give you the real solution to use the Form objects instead of the VB one.
You can find it on Form toolbar instead of Visual Basic toolbar

Another solution can be saving global variables in cells of sheet that will not be deleted, and using of OnTime method for restoring global variables as well as for re-initialization of classes etc. Below is the simple example for one global variable restoring after deleting sheet with ActiveX object:

Code:
Public X

Sub Init()
  ' Call this before sheet deleting
  X = 10
End Sub

Sub Test()
  ' Show value of the global X
  MsgBox "X=" & X
End Sub

Sub KillSheet()
  ' Initialization
  Call Init
  ' Save the global variables in cells, i.e. X in named cell [SavedX]
  [SavedX] = X
  ' Charge restoring of the saved global variables immediately after resetting
  Application.OnTime Now, "Restore"
  ' Kill the sheet with VBA Activex object
  Application.DisplayAlerts = False
  ActiveSheet.Delete
End Sub

Sub Restore()
  ‘ Restoring values of the global variables
  X = [SavedX]
  MsgBox "Restoring X=" & X
  ' Call classes re-initialization etc
  ' ...
End Sub
Regards,
Vladimir
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,624
Messages
5,469,777
Members
406,669
Latest member
Rubrob

This Week's Hot Topics

Top