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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
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,450
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,316
Messages
5,836,600
Members
430,441
Latest member
SurendraTantia

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