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

 

[Ruddles]

If I understand you correctly, you can. This is an example for putting A1:C3 into the message:-

.Characters.Text = ws.Range("A1") & vbTab & ws.Range("B1") & vbTab & ws.Range("C1") & vbNewline _
        & ws.Range("A2") & vbTab & ws.Range("B2") & vbTab & ws.Range("C2") & vbNewline _
        & ws.Range("A3") & vbTab & ws.Range("B3") & vbTab & ws.Range("C3") & vbNewline

Modify as required. Shout if it doesn't work for you.

 

[Dice711]

Wow... that was exzctly what I was looking for... thanks!!! You are the best!!!

 

[Rick Rothstein]

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

Those of you who follow my postings know about my preference for compact code, especially one-liners, so who am I to disappoint. The following one-liner will pop up a message box that will display for (approximately) 3 seconds unless the user clicks the OK button before the full 3 seconds have expired...

Sub TimedMessageBox()
  CreateObject("WScript.Shell").PopUp "Your Message goes here", 3, "Title for MessageBox goes here"
End Sub

Here is the link where I got this gem from (it contains full details on all you can do with it)...

Popup Method

 

[Ruddles]

Give that man a kewpie doll!

 

[xunhonee]

How to make this code work in a protected worksheet? I've got "the specified value is out of range" error when use in protected worksheet.

 

[Ruddles]

How to make this code work in a protected worksheet? I've got "the specified value is out of range" error when use in protected worksheet.

Without testing it for myself, perhaps you could unprotect the sheet at the start of the code, then protect it again after the code has run. Use the macros recorder to generate the appropriate code as a basis to work with.

 

[xunhonee]

Without testing it for myself, perhaps you could unprotect the sheet at the start of the code, then protect it again after the code has run. Use the macros recorder to generate the appropriate code as a basis to work with.

yeah, thanks a lot. I did slot in below codes and it worked.

ActiveSheet.Unprotect Password:=""
'
'
'
ActiveSheet.Protect Password:=""

 

[Michael M]

@peps1man
The code works fine for me in 2013, so can't see why it wouldn't work in 2010 !!
However, give Ricks snippet a try in the later post.....far more efficient AND shorter !!!