Need a better understanding about Public Variables

[Pookiemeister]

I am trying to get a better understanding how to declare Public variables and call them in various subs (mainly Private Subs). I am using the message box to test my code. O.K. so just below the Option Explicit and above a Private Sub, I declared a Public variable.

Option Explicit


Public mySheet As Worksheet

Private Sub cmdbtnOpen_Click()
    
    Set mySheet = ActiveSheet
    MsgBox mySheet.Name
    
End Sub

The above code works as expected. However the code below displays a Run-time error 424 Object Required.

Private Sub cmdbtnDone_Click()


    MsgBox mySheet.Name
    
    
End Sub

Is it because I didn't do the following code before my message box.

Set mySheet = ActiveSheet

Or should I of just created a Public Sub that displays the following inside a module and called it.

Public Sub Messagebox()
    Set mySheet = ActiveSheet
    MsgBox mySheet.Name
End Sub

Thank You.

 

[AlphaFrog]

Is it because I didn't do the following code before my message box.

Set mySheet = ActiveSheet

You don't have to define MySheet immediately before using it, but you do have to define it at sometime before using it. It could be in another procedure as long as it's before. Otherwise it's just an empty variable with no value.