CODEWITHSUNDEEP
vbaexcel
x189
x189

Reputation: 21

VBA - Reference an object by using a variable

Not sure how to reference the worksheet object with a variable that changes each time a sheet is activated.

The point is to reference a cell value based on the last worksheet that was activated (this code affects Sheet1 which does not set the variable when activated)

--Module1
  Public MyWS as String

--Sheet3 (Deactivation)
  MyWS = Sheet3.Codename

--Sheet2 (Deactivation)
  MyWS = Sheet2.Codename

--Sheet1
  Sheet1.Range("A3").Value = MyWS.Range("A3").Value

Updated:

Thanks for all the guidance but your instructions are not working for my project at least.

Sheet5.Range("C4").Value = Worksheets(MyWS).Range("A2").Value

Subscript out of range error when the above code is executed on Sheet5 deactivate.

MyWS is declared as a public string. MyWS is assigned the Sheet5.CodeName string when Sheet5 is activated. Sheet5 exists and that is the unmodified codename of the sheet. I can not use the user defined name of the sheet because that can change.

Upvotes: 2

Views: 4480

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

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

Upvotes: 2

Vityata
Vityata

Reputation: 43593

With ActiveSheet as mentioned in the comments is really the best solution.

However, if you want to do it "your way", write these Activate events in every worksheet:

Private Sub Worksheet_Activate()
    lastWS = Me.Name
End Sub

Then lastWs would be the name of the ActiveSheet. And you would be able to refer to it like this Worksheets(lastWs). Thus:

Sheet1.Range("A3").Value = Worksheets(lastWs).Range("A3").Value

Upvotes: 1

Related Questions