Public MyWS As String declares a String variable, not an object.
CodeName
The CodeName property returns a String that contains an identifier that VBA uses to generate a project-scoped object variable for a Worksheet; in the properties toolwindow (F4), that's the (Name) property.
This is how such code is legal:
Sheet1.Range("A3").Value = 42
Because Sheet1 has a code name string that returns Sheet1. Note that this identifier isn't necessarily the sheet's name (it is by default though), which the user can change at any time without accessing the Visual Basic Editor.
So if you rename the "Sheet1" tab/sheet to "Summary", but don't change its code name, then it will still be Sheet1 in code - so these two instructions do exactly the same thing:
Sheet1.Range("A3").Value = 42
ThisWorkbook.Worksheets("Summary").Range("A3").Value = 42
Now, if you want an object variable holding a reference to a worksheet that exists at compile-time, you already have one - Sheet1 is exactly that.
If you added a worksheet a run-time (doesn't exist at compile-time), then there's no such project-scope object variable for that sheet; that's when you need to declare your own, and assign it with the Set keyword:
Dim someSheet As Worksheet
Set someSheet = ThisWorkbook.Worksheets.Add
ActiveSheet
The Excel object model also has the ActiveSheet object, which returns whatever sheet is currently active.
Sheet1.Range("A3").Value = ActiveSheet.Range("A3").Value
Notice the explicit qualifiers. If it's written in a standard module (.bas), this code is equivalent:
Sheet1.Range("A3").Value = Range("A3").Value
If it's written in the code-behind of a specific worksheet module, then the above code will instead be doing this:
Sheet1.Range("A3").Value = Me.Range("A3").Value
Where Me is whatever the specific worksheet module you're in is, so if you're writing that code in a worksheet module, you will want to explicitly qualify the Range member call with the ActiveSheet object.
Worksheet Events
If you need to execute code when a worksheet is activated, you can handle the SheetActivate event in the ThisWorkbook module:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sheet As Worksheet
If TypeOf Sh Is Worksheet Then
Set sheet = Sh
Else
'Sh is not a worksheet. could be a chart sheet, or something else.
Exit Sub
End If
Debug.Print sheet.Name & " activated!"
End Sub
If you need to handle the Activated event of a specific worksheet that exists at compile-time, you need an event handler for it in that worksheet's code-behind:
Private Sub Worksheet_Activate()
Debug.Print Me.Name & " activated!"
End Sub
If you need to handle that event for a worksheet that is created at run-time, you need a WithEvents object variable in a class module (.cls):
Private WithEvents MySheet As Worksheet
And then you can write a handler for MySheet_Activate in that module, but that's more advanced stuff and I'm barely scratching the surface here, but that should get you going :)
