Close Hidden Data Sheet

[Guest]

I have a workbook that uses a hidden workbook for data. For the most part,
everything works, but there is one minor glitch.

For closing, I use:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks("HRData.xls").Close False
End Sub

When the user closes the workbook, the dialog box to save comes up with YES,
NO, CANCEL. If the user hits CANCEL, the HRData.xls is already closed and
the user no longer has access to the data. Is there a better way to close
the hidden workbook?

thank you,

 

[Guest]

Try:
Application.DisplayAlerts = False

Swisse

 

[Tom Ogilvy]

Use the workbook_SelectionChange event. Have it check if the data workbook
is open. If not, open it.

 

[Lonnie M.]

Hi,
I agree with swisse, and would add that in general it is a good idea to
set display alerts back to true:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Workbooks("HRData.xls").Close False
Application.DisplayAlerts = True
End Sub

Regards, Lonnie M.

 

[Guest]

Rightly said Lonnie.

Swisse

 

[Guest]

Tom,
Can you possibly expand on that? I am not familiar with that event. Thank
you,
Jeff

 

[Tom Ogilvy]

Just for clarification, I assumed you didn't want to prevent the user from
seeing this dialog. If you want to prevent the user seeing the dialog, see
the answers from Swisse and Lonnie M.

 

[Tom Ogilvy]

If you are using the workbook_beforeclose event, then go to that module and
in the right dropdown select SheetSelectionChange

This assumes you don't want to suppress the prompt.

this event will fire everytime the user selects a different cell. You could
check it your data workbook is open and then open it.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Dim bk as Workbook
On Error Resume Next
set bk = Workbooks("HRData.xls")
On Error goto 0
if bk is nothing then
Workbooks.Open thisworkbook.Path & "\HRData.xls"
End if
End Sub

See Chip Pearson's page on Events if you weren't just confused because I
said Workbook Level SelectionChange rather Workbook Level
SheetSelectionChange.

http://www.cpearson.com/Excel/events.htm