View Full Version : How can you use a variable to reference a worksheet object?
Module 1
Public LastWS as String
Sheet 3
(Activate Event)
LastWS = Me.Codename
Sheet 2
(Activate Event)
LastWS = Me.Codename
Sheet 1
(Activate Event)
Sheet1.Range("A3").Value = LastWS.Range("A2").Value
And yes the value of LastWS exists before the activate event occurs for Sheet1.
I have even tried cstr() with Sheets() and then Worksheets(LastWS) but nothing I try works. Everything gets thrown a subscript error. I need to use a variable to reference the worksheet by string because the last active sheet changes and that value determines what happens next.
Bear in mind the reference to the object will be inside a for/next loop as well. Thank you.
mdmackillop
04-20-2018, 10:21 AM
'Module 1
Public LastWS As Worksheet
'Sheet Modules 2 & 3
Private Sub Worksheet_Activate()
Set LastWS = ActiveSheet
End Sub
'Sheet Module 1
Private Sub Worksheet_Activate()
Range("A3").Value = LastWS.Range("A2").Value
End Sub
Paul_Hossler
04-20-2018, 12:14 PM
You're using LastWS as if it were an object of Type Worksheet
Sheet1.Range("A3").Value = LastWS.Range("A2").Value
LastWS is a string with a .CodeName in it
I think you wanted to use a string as an index / subscript into the Worksheets collection like this
Sheet1.Range("A3").Value =Worksheets( LastWS).Range("A2").Value
Personally, I think Mac's approach is cleaner since it strictly deals with the objects directly
Guys here's the problem. I have tried both of these methods. It keeps throwing a Object Variable or With Block Not Set. Here is a look at the current IDE.
Private Sub Worksheet_Activate()
If LastWS.CodeName = "" Then Exit Sub
MsgBox LastWS.CodeName
For x = 1 to 3
Sheet1.Range("A" & x).Value = LastWS.Range("A" & x).Value
Sheet1.Range("B" & x).Value = LastWS.Range("B" & x).Value
Sheet1.Range("C" & x).Value = LastWS.Range("C" & x).Value
Next x
End Sub
Public LastWS As Worksheet (Module 1)
Even with MsgBox LastWS.Codename alone in the sub it throws the same error.
So evidently it looks like even though you use Set LastWS = ActiveSheet in the other deactivate events for other sheets.. it seems the value is not being stored? Because if I use the following code it works.
Private Sub Worksheet_Activate()
Set LastWS = Sheet3
MsgBox LastWS.CodeName
Sheet1.Range("A3").Value = LastWS.Range("A3").Value
End Sub
So on the activate event
Set LastWS = ActiveSheet
works fine but then once you deactivate the worksheet the value seems to be lost switching back to Sheet1 from any sheet in between 2 and 5 (5 total)
Because I get an object with error unless I use Set LastWS in Sheet1 which is where the code above will compile
So the solution appears to be
Activate a sheet when the workbook is open and all seems to work accordingly
Module 1 Code
Dim LastWs As Worksheet
Sheets 2 and 3 Code
Private Sub Worksheet_Activate()
Set LastWs = Me
End Sub
Sheet1 code
Private Sub Worksheet_Activate()
MsgBox LastWS.CodeName
For x = 1 to 3
Sheet1.Range("A" & x).Value = LastWS.Range("A" & x).Value
Sheet1.Range("B" & x).Value = LastWS.Range("B" & x).Value
Sheet1.Range("C" & x).Value = LastWS.Range("C" & x).Value
Next x
Set LastWs = Nothing
End Sub
BTW, I'm not sure that you can refer to an object in the Sheets Collection by CodeName
I gave Sheet1 a codename of Sheet11, but
X = Sheets("Sheet11").Name
Raises an Error.
Paul_Hossler
04-20-2018, 05:04 PM
Maybe try this
In a standard module
Option Explicit
Public LastWS As Worksheet
In ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
Set LastWS = ActiveSheet
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "Leaving " & LastWS.Name & " for " & Sh.Name
If Sh Is Sheet1 Then
Sheet1.Range("A3").Value = LastWS.Range("A2").Value
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set LastWS = Sh
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.