asked on 5/12/2021

Excel VBA: How to trigger UserForm Activate code

Hello Team

I have the following code within a userform called frmSendEmail

Private Sub UserForm_Activate()
Form_Personalized = FindWindowA(vbNullString, Me.Caption)
STYLE = StartWindow(Form_Personalized, STYLE_CURRENT)
STYLE = STYLE Or WS_CX_MINIMIZAR
STYLE = STYLE Or WS_CX_MIXIMIZAR
MoveWindow Form_Personalized, STYLE_CURRENT, (STYLE)
End Sub

Open in new window

Within module "SendEmail" the following code is included:

Answer = MsgBox("Do you wish to check report details before producing email and attachment?", vbYesNo, "Mailbox Check")
 
  If Answer = vbYes Then
    frmSendEmail.Show
   Exit Sub
   
   Else
 
  End If

Open in new window

When the above code is executed, it does result with the display of the frmSendEmail but the form that is displayed does not include the display of the Maximize and Minimize functions on the userform which would be included if the Private Sub UserForm_Activate() was executed when the frmSendEmail.show is ran.

Can you please advise what I must adjust or include in order for the Private Sub UserForm_Activate() to be triggered when the frmSendEmail.show is executed?

Thank you in advance.

Dale

VBA Microsoft Excel Microsoft Office

Last Comment

Martin Liss

5/12/2021

Martin Liss

5/12/2021

In the IDE have you gone to the Debug menu and clicked Compile VBAProject?

Put a breakpoint on line 2 of the UserForm_Activate code. You do that by clicking in the left-hand margin of that line. Then execute your SendEmail code. Does the code stop at the breakpoint?

For more information about debugging, please see my article on the subject.

Dale James

5/12/2021

ASKER

Hello Martin

Thank you for your response.

Yes, I have done the compile and also applied a debug too the Activate sub procedure.

When I run the frmSendEmail.Show code, the procedure doesn't pass to the frmSendEmail module. The form just appears on screen without the debug process being activated.

If I run the Activate code from within the frmSendEmail module, the form is then displayed with the Max and Min functions included but not when the frmSendEmail.Show code is executed from another module.

byundt

5/12/2021

I wish you had posted a workbook. Because variables are declared and defined outside the scope of the code you posted, we can't trace the code, inspect values or debug. All we can do is guess.

It may be that WS_CX_MINIMIZAR and WS_CX_MIXIMIZAR are undefined when you execute the code in module SendEmail. I also wonder if that second variable should have been WS_CX_MAXIMIZAR (MAX rather than MIX).

ASKER CERTIFIED SOLUTION

Martin Liss

5/12/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Dale James

5/12/2021

ASKER

Martin...you are correct!!

The inclusion of the vbModal has triggered the userform to display the Max and Min functions.

Thank you very much!!

Hello byundt

Thank you for your input as well.

Sincerely

Dale

Martin Liss

5/12/2021

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020