Workbook close when Hidden not working - dynamic file location/filename

hensleyj

New Member
Joined
Apr 2, 2012
Messages
39
Hey everyone,

This has been bustin my balls all day.

I have a workbook which when opened, also opens another workbook which has visible = false set.

When i exit the main workbook, the other workbook does not close. I have tried multiple things to get it to close without luck.

I need a workbook close event which gets the file path and file name from;

Sheet: Ranges
File Path:I387
File name: L387

And then tells it to close even when it is hidden.

The file that opens hidden, will vary and the data held in "Ranges" confirms the file which is open.

Does anyone know how i could do this on a workbook close event? Or is there another way?

I tried quit application, which didn't work. I did try Application.Run with the location of the macro on the hidden workbook, but this had problems also.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
Try something like this. The blue code is the file name.

Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel As Boolean)
    Application.Workbooks([COLOR=#0000ff]Sheets("Ranges").Range("L387").Value[/COLOR]).Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:

hensleyj

New Member
Joined
Apr 2, 2012
Messages
39
That worked great! thanks heaps mate. I haven't worked too much with dynamic references like this so this will greatly help me moving forward.

There was a problem with the code in instances where there wasn't a workbook open in L387, so i set it up like this which works perfect for anyone who might also wonder if they read this,

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    nowbs = Application.WORKBOOKS.Count
     
    If nowbs >= 2 Then
         
        Application.ScreenUpdating = False
        Application.WORKBOOKS(Sheets("Ranges").Range("L387").Value).Close SaveChanges:=False
        Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it)


         
    Else
    Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it)
    End If
End Sub
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
You're welcome.

This could work as well. It just ignores the error if there is no other workbook with the L387 name.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.Workbooks(Sheets("Ranges").Range("L387").Value).Close SaveChanges:=[color=darkblue]False[/color]
    Application.ThisWorkbook.Saved = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,599
Messages
5,597,105
Members
414,125
Latest member
iQQ

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