public variables losing state after sheet deletion

[Guest]

I have a workbook where I declare some public variables such as:

Public wsControl as Worksheet

and then I have an initialization method that runs on Workbook_Open that
sets wsControl = Sheets("Control")

This works all fine and the reference sticks...until a sheet is deleted.
Then all my sheet references like the one above get set to Nothing. Is there
a way to avoid this or a way to determine when the state has changed in order
to re-initialize the public variables (without having to check everytime I
call a method)?

 

[RB Smissaert]

Doesn't happen here.

In ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
Set shSheet = Sheets("Sheet1")
End Sub

In normal module:

Option Explicit
Public shSheet As Worksheet

Sub test()

MsgBox shSheet.Name

End Sub

Delete Sheet2 and Sub test() still gives right result.

I think something else is causing your problem.


RBS

 

[Guest]

I tried this two different ways. It seems that if a user deletes a sheet, the
public variable is OK. If the sheet is programatically deleted, then the
public variable goes to nothing. I guess I have to reinitialize after every
sheet deleted

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

 

[Dave Peterson]

I did this in xl2003:

Behind the ThisWorkbook module:
Option Explicit
Private Sub Workbook_Open()
Set wks = Worksheets("sheet1")
End Sub

In a General module:
Option Explicit
Public wks As Worksheet
Sub aa()
Debug.Print "Before: " & wks.Name
Application.DisplayAlerts = False
Worksheets("sheet2").Delete
Application.DisplayAlerts = True
Debug.Print "after: " & wks.Name
End Sub

From the immediate window:
Before: Sheet1
after: Sheet1

If you killed any running code (hit the Reset button or used Run|Reset within
the VBE), or used a line like "End" (not "end sub" or "end if" or...), then the
public variable will lose its contents.