Text box instead of MsgBox

J

JohnMitchell

New Member
Joined
Jun 8, 2010
Messages
10
Alternative to MsgBox if you're just notifying the user about
something. User doesn't have to click OK.

Code: 
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
JohnMitchell said:
Alternative to MsgBox if you're just notifying the user about
something. User doesn't have to click OK.

Code: 
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
Click to expand...

Thanks for sharing this with the board. I've already got some ideas on how too put it into play.
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
as a suggestion, you could give OP option of specifying where in worksheet textbox is displayed.
Something like this maybe:

Rich (BB code): 
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Text Boxes - How to populate them without overlapping, bumping in to each other
Replies
3
Views
785
dcoker
D
M
  • Question
Trying to have Text Box removed after a 5 second delay
Replies
3
Views
1K
JLGWhiz
JLGWhiz
L
  • Question
CheckBox & Text Box with Multiple Functions
Replies
1
Views
391
dmt32
D
T
  • Question
.AddShape not appearing when I want it to (vba)
2
Replies
18
Views
837
tygrrboi
T
K
  • Question
Update to VBA that loops through Charts and Copies to PowerPoint to include text boxes
Replies
3
Views
1K
Brejne
B

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top