How to use VBA Code in Excel to display a balloon
Category: [Excel] Demo Available
How to use VBA Code in Excel to display a balloon
Requirement:
How to have balloons appear in a cell.
Solution:
Here is some code that will display a balloon.
OPen VBE , ALT+F11, and add a code module.
Then copy this code into it.
Code:
Sub ShowHelp()
Dim BalMsg As Balloon
Dim intChoice As Integer
Dim strReply As String
Set BalMsg = Assistant.NewBalloon
With BalMsg
.Animation = msoAnimationGetAttentionMajor
.Mode = msoModeAutoDown
.Icon = 4
.BalloonType = 0
.Heading = "J & R Excel Solutions"
.Text = "Hello " & Application.UserName & " can you help?"
.Labels(1).Text = "Get a banana."
.Labels(2).Text = "Find Tarzan."
.Labels(3).Text = "Gone fishing"
.Labels(4).Text = "Whatzup Doc?."
.Button = 1
intChoice = .Show
Select Case intChoice
Case 0
strReply = "Are you just clicking me for fun, " & Application.UserName
Case 1
strReply = "Thanks, " & Application.UserName & ", but I prefer a beer!"
Case 2
strReply = "Go find him yourself, lazy"
Case 3
strReply = "Haven't you got work to do?"
Case 4
strReply = "Nothing, just chillin...."
Case Else
strReply = "Nothing doing?"
End Select
End With
MsgBox strReply, vbInformation
End Sub
If you have added the code you should now be able to see ShowHelp as a macro you can run.
ALT+F8 to display a list of available macros.
Eventually you will run the macro by assigning the macro to a button or possibly a cell selection.
But lets take it one step at a time 
The user's main need, to be specific is when the user clicks in a Cell the user gets the balloon message the user selected for that cell.
You need to put some code in the worksheet selection event.
Right click the sheet tab and paste select View code.
Post the following in to the code module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
ShowHelp
End If
End Sub
Now every time you return to the cell A1 the help balloon should display.
Obtained from the OzGrid Help Forum.
Solution provided by Andy Pope.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy data in VBA from different named workbook each time |
| How to use VBA code to clear cells based on specific criteria |
| How to convert split formula in VBA in their respective columns |
| How to use VBA code - Find value from cell in different column and multiply by another cell |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.