Balloon button

S

sisco98

good morning everybody,

i would like to use message balloons (like the help menu) in macro to write
various messages/yes or no functions, etc. instead of messageboxes.
thanks in advance!
 
D

dominicb

Good morning Sisco98

You can utilise the office assistant if you so wish. The code below
will give you an idea on how to start using it, so copy it inot a
module and have a tinker with it.

Sub Test()
With Assistant.NewBalloon
..Heading = "Look at me"
..Text = "Here's how I destroy rubbish. Do you like it?"
..Button = msoButtonSetYesNo
..BalloonType = msoBalloonTypeButtons
..Mode = msoModeModal
..Animation = msoAnimationEmptyTrash
..Show
End With
End Sub

HTH

DominicB
 
H

Harald Staff

Hi

Those are found in the Autoshapes section of the Drawing toolbar. Record
macros while creating / modifying for clues on code syntax.

HTH. Best wishes Harald
 
N

Nick Hebb

The difference, of course, is that message boxes have an OK button to
close them, whereas the user would have to manually delete the
autoshape to get rid of the message.

The other problem with programmatically setting the text for an
autoshape is sizing it. I haven't worked with the balloon shapes, but
I know that the autosize property for the flowchart shapes doesn't wrap
the text. So if you have a long message, setting the autosize property
will create a really wide, single-lined shape.

If you discover an easy way, let me know.

You may be able to make an API call (using GetTextMetics, I think) to
get the font characteristics then calculate the proper height and
width, though - if you want to take it that far.
 
H

Harald Staff

Nick Hebb said:
The difference, of course, is that message boxes have an OK button to
close them, whereas the user would have to manually delete the
autoshape to get rid of the message.
Click to expand...

One button is easy; assign a macro to it. Quick and dirty sample:

Sub Test()
Dim s As Object
Set s = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangularCallout, _
125.25, 63.75, _
88.5, 78.75)
s.Select
Selection.Characters.Text = "Hi folks." & Chr(10) & Chr(10) & _
"Yall OK then ? Click me if so."
s.OnAction = ThisWorkbook.Name & "!DeleteMe"
ActiveCell.Select
End Sub

Sub DeleteMe()
Dim s As Object
On Error Resume Next
Set s = ActiveSheet.DrawingObjects(Application.Caller)
s.Delete
End Sub

It's at Yes/No , Ok/Cancel the trouble begins.

Best wishes Harald
 
N

Nick Hebb

s.OnAction = ThisWorkbook.Name & "!DeleteMe"

Coolness! I've only used OnAction for command bar buttons.
 
N

needurgenthelp

Hey folks, can anyone tell me how to re-activate the office assistant in
Excel 2007-I mean the Assistant has only been deprecated (hidden) so there
must be a way to 'unhide' that thing. Alternatively, can anyone shed any
light on what else can be used instead of balloons to let users make a
selection (i.e. chose printer) ?!?! Any help would be highly appreciated!!!!!
 
Top