View Full Version : [SOLVED:] Deactivate Event Causing Error
accell
09-16-2013, 11:30 AM
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
09-16-2013, 11:38 AM
Try fully qualifying this line
ThisWorkbook.Sheets(LogSheet).Cells(Rows.Count, BaseCol).End(xlUp).Offset(0, 1).Value = Now
accell
09-16-2013, 11:51 AM
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.
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
09-16-2013, 03:17 PM
Now im gettting this error on every deactivation:
Run-Time error '438':
Object doesn't support this property or method
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
09-16-2013, 06:30 PM
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
They both work for me. That is, both your's and mine
Paul_Hossler
09-17-2013, 06:04 AM
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
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
Aflatoon
09-18-2013, 02:54 AM
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
09-20-2013, 01:36 PM
Adding the sheet reference to the 'With ThisWorkbook' statement fixed the issue. Thanks alot guys
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.