Link to home
Start Free TrialLog in
Avatar of Cactus1993
Cactus1993Flag for United States of America

asked on

Excel 2007 Pop Up Macro Question

The current macro in my Excel 2007 workbook looks like the following, and works fine:


Private Sub Workbook_Open()
If Month(Now()) = 12 And Day(Now()) < 8 Then MsgBox "Happy New Year! Remember to RESET your statistics from last year before you log any new runs. Go to the very bottom of the Charts and Data Tab for instructions and an automated process. (In case you forget or haven't logged in, this pop-up will appear until January 7th.) Happy New Year!"
End Sub


I simply want the appearance to be as it looks below:



                                                           Happy New Year!

       Remember to RESET your statistics from last year before you log any new runs.

Go to the very bottom of the Charts and Data Tab for instructions and an automated process.

      (In case you forget or haven't logged in, this pop-up will appear until January 7th.)

                                                          Happy New Year!



Is this possible to do, with line breaks, blank lines in-between, and having it all being centered within the pop-up box?

Thanks.
Avatar of MINDSUPERB
MINDSUPERB
Flag of Kuwait image

Try this:

Private Sub Workbook_Open()
If Month(Now()) = 12 And Day(Now()) < 8 Then
MsgBox "Happy New Year!" & vbNewLine & "Remember to RESET your statistics from last year before you log any new runs." & vbNewLine & " Go to the very bottom of the Charts and Data Tab for instructions and an automated process." & vbNewLine & "(In case you forget or haven't logged in, this pop-up will appear until January 7th.) & vbNewLine & Happy New Year!"
End If
End Sub

Sincerely,
Ed
ASKER CERTIFIED SOLUTION
Avatar of MINDSUPERB
MINDSUPERB
Flag of Kuwait image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cactus1993

ASKER

It's not quite what I was looking for, but it looks okay.

I thought there might be a coding command that would center the text within the pop-up box, as opposed to having to manually add spaces in a guesstimate to center it.

In order to make it look like the attached screen shot, I had to delete about a half-dozen spaces in the coding you provided. I'm assuming either the differences in Excel programs, screen resolutions, or something else made it off center in my workbook.

The lines longer than the width of the pop-up box also start on the second line, flush with the left margin, as opposed to being centered.

I didn't know the basic commands for the "empty" and "next lines", so this definitely helped. If no one else provides specific code that centers all the lines, including the overflow line of sentences longer than the width of the pop-up box, I'll go with your answer.

Thanks, Ed! User generated image
You are right, you need really to put spaces to center it. Centering text is not available in a message box by VBA code.

Ed
Avatar of Rob Henson
Create a User Form instead with a single OK button that just closes the User Form.

Thanks
Rob H
See attached.

Thanks
RH popup.xls
MINDSUPERB: Okay ... I didn't know if centering text was available in a message box or not. That answers that. Thanks!

ROB: Thanks for your suggestion, but I'm really out of my element with User Forms. I'm also wondering and maybe falsely assuming that a User Form is a static element that must be placed somewhere on the worksheet, and only when clicked shows the pop-up? Or does it appear on the worksheet when you want it to (i.e., upon certain dates or events) when you want it to?

Thanks to you both.
The form would be the Pop Up message. maybe should have been a bit more explicit with my uploaded example.

Your code would have been amended from:

Private Sub Workbook_Open()
If Month(Now()) = 12 And Day(Now()) < 8 Then
MsgBox "blah..."
End If
End Sub

Open in new window

to:

Private Sub Workbook_Open()
If Month(Now()) = 12 And Day(Now()) < 8 Then

UserForm1.Show

End If
End Sub

Open in new window

The form will then show when required with text box embedded in it with the required text, formatted as required, and a button to OK which would then close the form and continue the script.

Thanks
Rob H
Rob:

That's good stuff. I haven't look much into User Forms, but based on what you've provided, I think I will. Seems more versatile, at least for formatting and all the various tools the user form can utilize.

Thank you for the follow-up here ... I really appreciate it!