Excel VBA to find & Close a different WB, then close main WB

jasonfish11

New Member
Joined
May 14, 2015
Messages
45
Hi,

I have a macro that winds up creating a new workbook based on some templates I have. There is some issues using these in Chrome, so I've added a warning at the beginning when a workbook is opened. Currently the warning appears twice, once when the 1st workbook is opened "Associate Movement Workbook" and once when the template is opened variable of "MainWB" in coding.

I want to get rid of one of the warnings, but to do so I need the template coding to close both workbooks without saving if the user selects the "Leave" button (indicating they are using Chrome).

The coding I have if I only have the MainWB open will close the MainWB. If I have both WB's open (how the user will experience it) then the macro will find and close the "Associate Movement Workbook" but does not close "MainWB". I can't figure out why this happens. Any help will be much appreciated.


VBA Code:
Private Sub UserForm_Initialize()
    ContinueCommand.SetFocus
End Sub

Private Sub LeaveCommand_Click()
Dim WB As Workbook
Dim MainWB As Workbook

Set MainWB = ActiveWorkbook

For Each WB In Workbooks
    If WB.Name = "Associate Movement Workbook.xlsm" Then
        WB.Close SaveChanges:=False
    End If
Next

MainWB.Close SaveChanges:=False
Unload ChromeWarning

End Sub

Thank you in advance.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Is the userform is part of the "main workbook" then try replacing in the above code the line
Rich (BB code):
MainWB.Close SaveChanges:=False
with
VBA Code:
ThisWorkbook.Close False
If it works then you may remove the Dim and the Set about MainWB

Bye
 

jasonfish11

New Member
Joined
May 14, 2015
Messages
45
Is the userform is part of the "main workbook" then try replacing in the above code the line
Rich (BB code):
MainWB.Close SaveChanges:=False
with
VBA Code:
ThisWorkbook.Close False
If it works then you may remove the Dim and the Set about MainWB

Bye

I've tried this, I've also tried the below coding. Neither worked.


VBA Code:
Private Sub UserForm_Initialize()
    ContinueCommand.SetFocus
End Sub

Private Sub LeaveCommand_Click()
Dim WB As Workbook
Dim MainWB As Workbook

Set MainWB = ActiveWorkbook

For Each WB In Workbooks
    If WB.Name = "Associate Movement Workbook.xlsm" or MainWB Then
        WB.Close SaveChanges:=False
    End If
Next

Unload ChromeWarning

End Sub
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
I want to get rid of one of the warnings, but to do so I need the template coding to close both workbooks without saving if the user selects the "Leave" button (indicating they are using Chrome).
I only tried addressing this your question, by replacing the concept of "ActiveWorkbook" (whichever it is) with "ThisWorkBook" (that, by definition, refers to the workbook that host the macro under execution)

Your If WB.Name = "Associate Movement Workbook.xlsm" or MainWB Then is syntactically wrong (you should use If WB.Name = "Associate Movement Workbook.xlsm" or WB.Name = MainWB.Name Then), but is also logically wrong (because you risk to close the workbook with the running macro, that would prevent the macro completion with the closing of the second file).

You even didn't confirm my hypotesis that the macro is hosted by what you call "the MainWB" (and not in "Associate Movement Workbook.xlsm", that would explain while only one workbook get closed; or in a third workbook)

Unfortunately, without a detailed description of your working area, your process and the full coding I am unable to formulate any rational hypotheses. Sorry.
 

jasonfish11

New Member
Joined
May 14, 2015
Messages
45

ADVERTISEMENT

sorry I missed that part. The macro is hosted in the MainWB
 

jasonfish11

New Member
Joined
May 14, 2015
Messages
45
Also the only part of the coding I removed from the above was related to a user hitting the "continue" button, as well as the code that launches the userform on startup. I didn't think it would be helpful. This coding is embedded in a userform that warns the user against using Chrome.

Here is the full coding.

In ThisWorkbook section...

VBA Code:
Private Sub Workbook_Open()

ChromeWarning.Show

End Sub

Full coding for the userform...


Code:
Private Sub UserForm_Initialize()
    ContinueCommand.SetFocus
End Sub

Private Sub ContinueCommand_Click()

Unload ChromeWarning

End Sub

Private Sub LeaveCommand_Click()
Dim WB As Workbook
Dim MainWB As Workbook

Set MainWB = ActiveWorkbook

For Each WB In Workbooks
    If WB.Name = "Associate Movement Workbook.xlsm" Then
        WB.Close SaveChanges:=False
    End If
Next

MainWB.Close SaveChanges:=False
Unload ChromeWarning

End Sub
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147

ADVERTISEMENT

If the code is hosted by the MainWB and the other workbook to close is named "Associate Movement Workbook.xlsm" then the following version of code should do the job:
Code:
Private Sub LeaveCommand_Click()
'
On Error Resume Next
Workbooks("Associate Movement Workbook.xlsm").Close False
On Error GoTo 0
DoEvents
ThisWorkbook.Close False
Unload ChromeWarning
'
End Sub
On Error Resume Next is there to continue the macro in case the first workbook is not found.

I understand that this is part of a userform named ChromeWarning; beware that the macro and the userform will be killed when "ThisWorkbook" get closed, and the instruction Unload ChromeWarning is there only "pro forma"

Also you must open your userform as "modeless", given that Microsoft remind us that "When a UserForm is modal, the user must respond before using any other part of the Application"; workbooks are property of Application. Closing workbooks while a "modal" userform is shown could give unpredictable results. Thus change to:
VBA Code:
ChromeWarning.Show vbModeless

Bye
 

jasonfish11

New Member
Joined
May 14, 2015
Messages
45
Hmmm odd, this is still only closing "Associate Movement Workbook" and leaving "MainWB" open.

If I only open "MainWB" without having the other one open, then the coding (including what I posted above) will close the "MainWB" but when I have both open it closes "Associate Movement Workbook" but does not close "MainWB"
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Which Excel version do you use? Is the userform open "vbModeless"?
If I only open "MainWB" without having the other one open, then the coding (including what I posted above) will close the "MainWB" but [...]
Which other code is used to close the workbooks?

bye
 

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,768
Members
416,879
Latest member
Excel_Newbie4980

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