Event Workbook_BeforeClose

[doubtfire]

I am trying to update a range for thirty worksheets using another workbook, the issue I have is before closing normally I will have a question asking users whether to do other stuff to the same worksheet.
Updating is great and the only thing is I have manually answer this question before going to the next workbook.
The question is asked under the event "Workbook_BeforeClose"
I have used
Application.DisplayAlerts = False
Application.ScreenUpdating = False
and I still to have respond to this question.
I have tried SendKeys, which reacts after the question. Or is there a way to know if the calling comes from another workbook to capture the action to identify this case.
Any suggestions? :banghead:

[Bob Phillips]

I have never been asked to do other stuff. Do you mean it asks if you want to save. If so, set Cancel to True.

[doubtfire]

Thanks to the rescue again.
I would like to save the worksheet since there is update, BUT not to the question just like it does not exist (normally the user will answer by themselves).
Again the Save is YES.

[Bob Phillips]

Just save it in that event

Private Sub Workbook_BeforeClose(cancel As Boolean)

Me.Save
cancel = True

End Sub

[doubtfire]

Gracias.
My original event is as followed (courtesy question is there as per company's request)


Private Sub Workbook_BeforeClose(cancel As Boolean) Dim result as Integer

Result = MsgBox("Do you want to do something else?" , vbYesNo, "Title1")
If Result = 6 Then 'Yes - 6 No - 7
Form1.show
End If

End Sub

[Bob Phillips]

Use the built-in constants

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim result As VbMsgBoxResult

result = MsgBox("Do you want to do something else?", vbYesNo, "Title1")
If result = vbYes Then
Form1.Show
End If
End Sub

[doubtfire]

Gracias.
Great tips for me.
Where do you think I should put in order to "remotely" answer this question
when I open it from another workbook for updating the worksheet?
Should I do something inside the Event?
Thank you!:think:

[Zack Barresse]

Can you explain what you mean by "remotely"? There is nothing remote about this code you have. It will fire when the command is given for that specific workbook to close, given that the EnableEvents property is True (it is by default unless otherwise set). If you mean you want to call this event from somewhere else, you will get this specific code to fire if, from the other workbook, you close it. Does that make sense?

[doubtfire]

Thank for the response.
I have a group of workbooks that I have to update a certain range, instead of updating them one by one I use another workbook with macros to "remotely" have those ranges modified.
The modification of those ranges run well and the only thing I have issue is there is a question asked every time before closing that those of workbooks.
And the question is placed on the event "Workbook_BeforeClose".
How can I ignore this question since I am not a normal user which should be asked before leaving the workbook?
Thank you.:(

[Zack Barresse]

If you don't want the events to fire, set the EnableEvents property to false.

Application.enableEvents = False

'... your code here, closing workbooks, whatever

Application.Enableevents = true

HTH

[doubtfire]

Thanks.