Deactivate Event Causing Error

[accell]

I am running a Workbook_WindowDeactivate event that seems to be working incorrectly when the workbook is deactivated by opening a downloaded Excel file (in protected view). It works fine when I switch between workbooks or open a file from the directory.

Error returned: Run-time error '1004' --- "Method 'Rows' of object '_Global' failed"

The section of code causing the error in ThisWorkbook:
("Sheets(Logsheet)..." line in the if statement highlighted by debugger)



Option Explicit
Const LogSheet As String = "Log"
Const BaseCol As String = "B"

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
If Len(ThisWorkbook.Sheets("Log").Range("B2")) > 0 Then
Sheets(LogSheet).Cells(Rows.Count, BaseCol).End(xlUp).Offset(0, 1).Value = Now
End if
End Sub


Any ideas how to prevent this? Thanks

[mikerickson]

Try fully qualifying this line

ThisWorkbook.Sheets(LogSheet).Cells(Rows.Count, BaseCol).End(xlUp).Offset(0, 1).Value = Now

[accell]

It still returns the same error. I have also tried this via if statement: If ThisWorkbook.Name = ActiveWorkbook.Name Then... but that didnt work wither.

[snb]

What if:


Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Wn.Sheets("log").Cells(Rows.Count, 2).End(xlUp).Offset(, 1).Value = Now
End Sub

[accell]

Now im gettting this error on every deactivation:

Run-Time error '438':

Object doesn't support this property or method

[SamT]

If the code is in the ThisWorkbook code page, try
Private Sub Workbook_Deactivate()
With ThisWorkbook.Sheets(LogSheet)
If Len(.Range("B2")) > 0 Then .Cells(Rows.Count, BaseCol).End(xlUp).Offset(0, 1).Value = Now
End With
End Sub

[Paul_Hossler]

This works for me. I was very explicit in referencing the WB and WS, including the dot in .Rows.Count, and some other objects.

It can get tricking dealing with 2 different WBs or WSs, so I tend to not make Excel assume things




Option Explicit
Const LogSheet As String = "Log"
Const BaseCol As String = "B"

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
With ThisWorkbook.Sheets("Log")
If Len(.Range("B2").Value) > 0 Then
.Cells(.Rows.Count, BaseCol).End(xlUp).Offset(0, 1).Value = Now
End If
End With
End Sub



Paul

[SamT]

They both work for me. That is, both your's and mine

[Paul_Hossler]

They both work for me. That is, both your's and mine

Yes, the OP can choose which event to hook: your WB_Deactivate or the Window_Deactivate one

Personally, I almost always use the WB_Deactivate one like you did

Paul

[snb]

This might also work:


Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Wn.Activesheet.Cells(Rows.Count, 2).End(xlUp).Offset(, 1).Value = Now
End Sub

I don't think you have to address the workbook and the sheet specifically since they are inherent in this event:

- Thisworkbook is equivalent to Workbook_ in the name of this eventcode
- WN is a window in thisworkbook


Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
msgbox Wn.parent.name
msgbox Wn.activesheet.name
End Sub

[SamT]

@ snb

:thumb

[Aflatoon]

The error indicates that unqualified use of Rows.Count will not work so any solution needs to qualify that call. ;) (this is often the case with workbooks opened in browser windows)

[accell]

Adding the sheet reference to the 'With ThisWorkbook' statement fixed the issue. Thanks alot guys