Global variables reset automatically

Question

I want to define a global variable as a worksheet on opening a file, so I have used the following code:

In Module1:

Public mySheet As Worksheet

In ThisWorkbook:

Sub Workbook_Open()  
    Set mySheet = Sheet1   
End Sub

I want to then use mySheet to refer to this particular worksheet throughout various procedures, some of which refer back to this worksheet having opened a new file.

It works initially - when I open the file the variable is set, and a macro involving mySheet.Unprotect, mySheet.Protect, and mySheet.Range("A1") works. However, when I try to run it again I get an error Object variable or With block variable not set, and the debug takes me to the mySheet.Unprotect line, which is the first time the sheet is referenced.

How can I define this worksheet in a global variable so that the definition sticks?

For reference, the particular macro I am referring to is below, although I have had a similar problem with different bits of code:

Sub mySub()
    mySheet.Unprotect 
    With Application.FileDialog(msoFileDialogOpen)  
        .AllowMultiSelect = False           
        If .Show <> 0 Then
            mySheet.Range("A1") = .SelectedItems(1)
        End If           
    End With

    mySheet.Protect        
End Sub

Answer 1

Personally I would suggest you create a Function that returns that sheet. Then you can basically use the function name as a Worksheet variable. (Although if you always want the same sheet you could just use the Sheet1 codename)

Function MySheet() As Excel.Worksheet
    Set MySheet = Sheet1
End Function

and use, for example:

Sub foo()
    MsgBox MySheet.Name
End Sub