View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
RB Smissaert RB Smissaert is offline
external usenet poster
 
Posts: 2,452
Default public variables losing state after sheet deletion

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