Trigger code to open 2nd workbook when 1st workbook is attempted to be closed

[kosherboy]

Hi,
The code below gets triggered before closing workbook but gets bugged.
The code is supposed to open another workbook before closing and clear a cell and then close again, this is granted that other workbook is editable.
Here's the code, I made the fonred where the code gets bugged.

Please Login or Register  to view this content.

[ikboy]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Updates database when main wkbk is closed
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim rcell As Long
Dim LRV As Long

Dim Path As String
Path = "\\user\DataBase.xlsx"
Dim DataBaseWb As Workbook: Set DataBaseWb = Workbooks.Open(Path)
Dim wsV As Worksheet: Set wsV = Sheets("OffersV") here in red, you should check that workbook has this sheets or not.
If Not DataBaseWb.ReadOnly Then

LRV = wsV.Range("AI" & Rows.Count).End(xlUp).Row
For rcell = 2 To LRV
If wsV.Range("AI" & rcell) = ws.Range("G9") Then 'why ws?
wsV.Range("BN" & rcell) = ""
Exit For
End If
Next rcell

DataBaseWb.Close True

Else

MsgBox "Data base currently in use, retry closing."

Cancel = True
DataBaseWb.Close False

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kosherboy]

Thank you John.
It works!
Question: Why did the "." make such a difference? Why can't i write the code in this procedure like i normally do?
Btw, i decided to place the code in a module and call the that code from the procedure and it worked

[kosherboy]

Hi ikboy,
Thanks for your reply.
Please see my comments below (in blue)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wsV As Worksheet: Set wsV = Sheets("OffersV") here in red, you should check that workbook has this sheets or not.checked and this sheet exists
If Not DataBaseWb.ReadOnly Then

LRV = wsV.Range("AI" & Rows.Count).End(xlUp).Row
For rcell = 2 To LRV
If wsV.Range("AI" & rcell) = ws.Range("G9") Then 'why ws?You're right, i forgot to declare it as a variable but the code bugged even before it got up to this point

[JOHN H. DAVIS]

Thank you John.
It works!
Question: Why did the "." make such a difference? Why can't i write the code in this procedure like i normally do?
Btw, i decided to place the code in a module and call the that code from the procedure and it worked

It's not the "." that's important. You need to define the workbook. DataBaseWb.Sheets("OffersV")

[kosherboy]

It's not the "." that's important. You need to define the workbook. DataBaseWb.Sheets("OffersV")

I hear, so why am i not required to define the workbook in regular modules?

The code understands this very well in modules:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

You would have too if you are working with two workbooks. In your case The Activeworkbook (the one you are closing) and DatabaseWb (the one you are opening and working with). My guess is the Activeworkbook does not have a Sheet("OffersV"). If you don't define the workbook then excel will look for Sheet("OffersV") in the Activeworkbook.

[kosherboy]

I think i will start defining workbooks as it does sound more solid. until now i have not been defining the workbooks when declaring sheets and it seems to have worked, it's the first time by the procedures that it's causing issues.