Close Hidden Data Sheet
 

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,
--
Jeff

Close Hidden Data Sheet
 

Try:
Application.DisplayAlerts = False

Swisse

Close Hidden Data Sheet
 

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

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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,
--
Jeff

Close Hidden Data Sheet
 

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.

Close Hidden Data Sheet
 

Rightly said Lonnie.

Swisse

Close Hidden Data Sheet
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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,
--
Jeff

Close Hidden Data Sheet
 

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Use the workbook_SelectionChange event. Have it check if the data
workbook
is open. If not, open it.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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,
--
Jeff

Close Hidden Data Sheet
 

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

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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,
--
Jeff