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


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

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


"J Streger" wrote in message
...
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)?


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

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



"RB Smissaert" wrote:

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


"J Streger" wrote in message
...
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)?


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

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 "Befo " & wks.Name
Application.DisplayAlerts = False
Worksheets("sheet2").Delete
Application.DisplayAlerts = True
Debug.Print "after: " & wks.Name
End Sub

From the immediate window:
Befo 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.



J Streger wrote:

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

"RB Smissaert" wrote:

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


"J Streger" wrote in message
...
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)?


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




--

Dave Peterson