Avatar of Dale James
Dale James

asked on 

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


VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Martin Liss
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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.
Avatar of Dale James
Dale James

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.
Avatar of byundt
byundt
Flag of United States of America image

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
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
Avatar of Dale James
Dale James

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo