(worksheet).delete method causes Project Reset !!

[stevebovy]

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

 

[RoryA]

What is the exact code? Are you using codename.delete or Worksheets("tab name").Delete?

 

[stevebovy]

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]

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]

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]

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]

If you don't need an ActiveX control, use a Forms toolbar version instead.

 

[erik.van.geit]

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

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]

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:

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