Require dependant secondary excel workbook to be saved and closed before primary may close using VBA -

i novice vba , looking help. have dashboard accesses secondary file users enter in information. wondering if there way require secondary workbook closed before close original dashboard has been launched.

as example: on close of fpa_opportunities_v6.xlsm if ccc_error_tracker.xlsm active require ccc_error_tracker.xlsm closed before fpa_opportunities_v6.xlsm can shut down.

the file required closed located @ "supporting_files\ccc_error_tracker.xlsm primary dashboard users launch located at:\\\opportunities_dashboard\fpa_opportunities_v6.xlsm.

right have set detect if fpa_opportunities_v6.xlsm dashboard read because team leaders should able make changes this. response provides user "no changes have been made authorized captain. if changes have been made ccs_error_tracker , prompted changes have been saved, you're go. please close window save settings."

i rather not confuse end user if possible. simple solution have require close. thoughts? feel beneficial other users

       private sub workbook_beforeclose(cancel boolean) dim ws worksheet sheets("start").visible = xlsheetvisible each ws in thisworkbook.worksheets if ws.name <> "start" ws.visible = xlveryhidden end if next ws coderetry:      on error goto failed         if me.saved = true , backupreqd = false exit sub     dim sdatetime string, sfilename string     thisworkbook         sdatetime = " (" & format(now, "yyyy-mm-dd hhmm") & ").xlsm"         sfilename = replace(.name, ".xlsm", sdatetime)         .savecopyas filename:="p:\wi\teams\programs\j&j ccc\chc & skincare\care specialist\alicia's team\fpa results\supporting_files\fpa_file_backups\opportunities_dashboard\" & sfilename       goto passed  failed:  goto coderetry   exit sub passed:     application.displayalerts = false     if thisworkbook.readonly = true goto passed2      goto passed3  passed2:     if isworkbookopen("p:\wi\teams\programs\j&j ccc\chc & skincare\care specialist\alicia's team\fpa results\supporting_files\ccc_error_tracker.xlsm")     msgbox "excel has detected `team error tracker` still open , not been saved. opportunities dashboard closing please remember in order save data must close ccc_error_tracker.", vbinformation     end if     goto end1 passed3: thisworkbook.save msgbox "your data has been saved , backed-up successfully! backup stored 72 hours before discarded save disk space. email chrischm@altaresources.com if have suggestion." end1: end end sub function isworkbookopen(workbookname string) dim ret boolean ret = false on error resume next     each wb in application.workbooks         if wb.name = workbookname             ret = true             goto earlyexit         end if     next on error goto 0 earlyexit:     isworkbookopen = ret end function 

let me know if there else others. want accurate possible. please provide tips thanks.

the simple way test if book closed test if it's no longer open.

this function check whether workbook (specified name) open, , return boolean true or false value can use in code logic check whether workbook still open.

for example:

if isworkbookopen("fpa_opportunities_v6.xlsm")     msgbox "a message box let user know need save & close first..., etc.", vbinformation end if 

here function:

function isworkbookopen(workbookname string) dim ret boolean ret = false on error resume next     each wb in application.workbooks         if wb.name = workbookname             ret = true             goto earlyexit         end if     next on error goto 0 earlyexit:     isworkbookopen = ret end function 

make sure put function outside of sub, after end sub good, or can put before sub ..., or put in different module if want.