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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
What is the exact code? Are you using codename.delete or Worksheets("tab name").Delete?
 
Upvote 0
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
 
Upvote 0
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?)
 
Upvote 0
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.
 
Upvote 0
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 ?????
 
Upvote 0
If you don't need an ActiveX control, use a Forms toolbar version instead.
 
Upvote 0
... 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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,218,656
Messages
6,143,731
Members
450,502
Latest member
sakaria_123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top