PDA

View Full Version : How can you use a variable to reference a worksheet object?



xmp1
04-20-2018, 09:23 AM
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

xmp1
04-20-2018, 02:16 PM
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.

xmp1
04-20-2018, 02:30 PM
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

xmp1
04-20-2018, 02:43 PM
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

xmp1
04-20-2018, 03:11 PM
So the solution appears to be

Activate a sheet when the workbook is open and all seems to work accordingly

SamT
04-20-2018, 03:51 PM
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