Userform in Before close event of workbook

[Raj]

Hi,

I am using an Userform in the Before close event of ThisWorkbook to
elicit a response from the user. The userform has 3 option buttons.
The userform has a command button on clicking of which processing is
done depending on the option button chosen. I want the control to
return from the Userform to the Before close event so that the rest of
the code in the before close event is then run.

How to do this?

Thanks in advance for the help.

Regards,
Raj

 

[Harald Staff]

Hi Raj

Simple demo. ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim UF As UserForm1
Set UF = New UserForm1
UF.Show
Select Case UF.MyValue
Case 1
MsgBox "One"
Case 2
MsgBox "two. Staying open"
Cancel = True
Case Else
End Select
Unload UF
End Sub

Userform1 module (with CommandButton1 and 2):

Public MyValue As Long

Private Sub CommandButton1_Click()
MyValue = 1
Me.Hide
End Sub

Private Sub CommandButton2_Click()
MyValue = 2
Me.Hide
End Sub

HTH. Best wishes Harald

 

[Raj]

Hi,

I am using an Userform in the Before close event of ThisWorkbook to
elicit a response from the user. The userform has 3 option buttons.
The userform has a command button on clicking of which processing is
done depending on the option button chosen. I want the control to
return from the Userform to the Before close event so that the rest of
the code in the before close event is then run.

How to do this?

Thanks in advance for the help.

Regards,
Raj

Hi,

I added an Unload Me in the Command button click code and the control
returned to the Before close event.

Regards,
Raj

 

[RyanH]

Call your userform from the BeforeClose Event like so.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call UserForm1

'the rest of your code

End Sub

I assume the processing code you are talking about is contained in the
CommandButton1_Click Event. If so, when the user clicks the command button
make sure the Userform is Unloaded (Me Unload) and the Before Close Event
will continue.

I think this is what you are saying.

 

[Harald Staff]

I added an Unload Me in the Command button click code and the control

returned to the Before close event.

Now I'm really curious. Why the Unload Me?

Best wishes Harald