We help IT Professionals succeed at work.

Excel 2007 Pop Up Macro Question

Cactus1993
Cactus1993 asked
on
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.
Comment
Watch Question

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
This is much better:

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


Ed
Cactus1993Owner

Author

Commented:
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! Pop-Up Box
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
Rob HensonFinance Analyst

Commented:
Create a User Form instead with a single OK button that just closes the User Form.

Thanks
Rob H
Rob HensonFinance Analyst

Commented:
See attached.

Thanks
RH popup.xls
Cactus1993Owner

Author

Commented:
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.
Rob HensonFinance Analyst

Commented:
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
Cactus1993Owner

Author

Commented:
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!