Text box instead of MsgBox

[JohnMitchell]

Alternative to MsgBox if you're just notifying the user about
something. User doesn't have to click OK.

Sub MsgTxtBox()
 
Dim ws As Worksheet
Dim box As Shape
 
Set ws = ActiveSheet
Set box = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 350, 180, 150, 50)
 
box.TextFrame.Characters.Text = "Thank You !"
box.TextFrame.HorizontalAlignment = xlHAlignCenter
box.TextFrame.VerticalAlignment = xlVAlignCenter
box.Fill.ForeColor.RGB = RGB(255, 255, 255)
box.TextFrame.Characters.Font.Color = RGB(255, 0, 0)
box.TextFrame.Characters.Font.Bold = True
box.TextFrame.Characters.Font.Size = 24
Application.ScreenUpdating = True
Application.Wait (Now + TimeValue("00:00:01"))
box.Delete
Set box = Nothing
 
End Sub

 

[John Davis]

Alternative to MsgBox if you're just notifying the user about
something. User doesn't have to click OK.

Sub MsgTxtBox()
 
Dim ws As Worksheet
Dim box As Shape
 
Set ws = ActiveSheet
Set box = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 350, 180, 150, 50)
 
box.TextFrame.Characters.Text = "Thank You !"
box.TextFrame.HorizontalAlignment = xlHAlignCenter
box.TextFrame.VerticalAlignment = xlVAlignCenter
box.Fill.ForeColor.RGB = RGB(255, 255, 255)
box.TextFrame.Characters.Font.Color = RGB(255, 0, 0)
box.TextFrame.Characters.Font.Bold = True
box.TextFrame.Characters.Font.Size = 24
Application.ScreenUpdating = True
Application.Wait (Now + TimeValue("00:00:01"))
box.Delete
Set box = Nothing
 
End Sub

Thanks for sharing this with the board. I've already got some ideas on how too put it into play.

 

[JohnMitchell]

Happy to share - I search the board a lot for help on 'stuckies'. Good to give back....

jm

 

[Ruddles]

And if you remove the Application.Wait and Box.Delete, you can fire this up saying "Please wait..." at the start of some long-running piece of code, update the text as the code progresses ("Tables loaded", "Analysing results", "Printing results", etc), then do the Box.Delete when the code finishes running.

 

[JohnMitchell]

Very nice!

 

[peps1man]

Hello,

I am looking for a similar function. I'm new to coding some excel 2010 pages and it seems that it doesnt work on 2010. Any ideas why ??

Is this for 2003/2007 ?

 

[Ruddles]

I don't know what the OP was using. I've run it okay under 2007.

When you say "it seems that it doesn't work on 2010", is that because you've tried it and it doesn't work?

 

[Dice711]

Hello John. I really like this pop-up text box function/feature. How would I create a new line (similar to & vbnewline) and possibly paste information into neat columns? Ultimately, I would like to just copy a small section of a spreadsheet (maybe 4 columns and 20 rows) and paste that information into a popup msgbox or text box.

Thanks in advance for any help and thanks for the new function.

Paul

 

[dmt32]

as a suggestion, you could give OP option of specifying where in worksheet textbox is displayed.
Something like this maybe:

Sub MsgTxtBox()
    Dim ws As Worksheet
    Dim box As Shape
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set box = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, 350, 180, 150, 50)
    With box
        .Left = ws.Range("G10").Left
        .Top = ws.Range("G10").Top
       .Fill.ForeColor.RGB = RGB(255, 255, 255)
        With .TextFrame
            .Characters.Text = "Thank You !"
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
            .Characters.Font.Color = RGB(255, 0, 0)
            .Characters.Font.Bold = True
            .Characters.Font.Size = 24
        End With
    End With
    Application.ScreenUpdating = True
    Application.Wait (Now + TimeValue("00:00:02"))
    box.Delete
    Set box = Nothing
End Sub

Dave

 

[Dice711]

Thanks Dave... interesting option. Wish I could just paste a small piece of spreadsheet info into the pop-up text box... similar to:

Act# Name Account Value Last Visit
1234 John Smith $43 3/1/2013
4321 Tim Jones $111 2/22/2013
1222 George Smith $55 3/11/2013
1333 John Jay $96 4/5/2013
1444 Mary Smith $22 1/12/2013

Except a bit more cleaned up into proper columns...

Paul