BeforeClose Event

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Can anybody please help with this error in my code
I have set up opening a source workbook using the Workbook_Open event, which works just great.


I need to then close the source workbook using the BeforeClose event without saving any changes. But I keep getting “Object required” error
Any help is appreciated code below
VBA Code:
Private Sub Workbook_Open()
Dim WB As Workbook
 Dim CurrentSheet As Worksheet
 Set CurrentSheet = ActiveSheet
Set Wkbk = ActiveWorkbook

    Application.ScreenUpdating = False ' turn off the screen updating
    Sheets("Running List").Select
       Set WB = Workbooks.Open("C:\Users\Gary Baker\Documents\Lion Engineering\W4G\W4G\Operational Data\_Databases\Customer List.xlsx", True, True)
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WB As Workbook
 Dim CurrentSheet As Worksheet
 Set CurrentSheet = ActiveSheet
Set Wkbk = ActiveWorkbook
Set WB = Workbook

    Application.ScreenUpdating = False ' turn off the screen updating

    WB.Close savechanges:=False ' close the source workbook without saving any changes
    Wkbk.Activate
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,543
Office Version
  1. 2010
Platform
  1. Windows
It seems you just forgot to initialize the useless variable object so try with the workbook name rather than via a variable …​
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,753
With Set WB = Workbook, you're trying to assign a class type to your variable, instead of assigning a specific workbook. You'll need to specify a workbook, for example...

VBA Code:
Set WB = Workbooks("Book2.xlsx")  'by name

or

VBA Code:
Set WB = Workbooks(1) 'by index

or

VBA Code:
Set WB = ThisWorkbook 'workbook running the code
 
Solution

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
413
Office Version
  1. 365
Platform
  1. Windows
Domenic
Brilliant, all so easy when you know, thank you so much.

Gary
 

Forum statistics

Threads
1,143,637
Messages
5,719,971
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top