How to display a balloon and place some text in it

I could be wrong, but I don't think you can control the mouse tips. You could use a work around by using the comment boxes. This code assumes you are on the first worksheet, and you don't have any other comment boxes. Just change the MyName = Format(Target.Value, "##") to call your function that spells out the number. Can I have a copy of that function?

Please Login or Register  to view this content.

This post may help you

http://www.excelforum.com/showthread...10#post1641610

Now, I have tried somethin else, with the macro recorder, created a balloon or cloud call out and pointed it at cell A1 , and then put a formula in the callout that refers to A1, when you are in the call out, just go to the formula bar and refer a cell, anyway here's the code that I recorded

Please Login or Register  to view this content.

now for example purposes I use the selection change event so these code could be activated, select A1 and the macro1 kicks in select A2 and the sheet deletes

Please Login or Register  to view this content.

Good luck !,I hope you are able to get some Ideas from this

Originally Posted by Mallycat

I could be wrong, but I don't think you can control the mouse tips. You could use a work around by using the comment boxes. This code assumes you are on the first worksheet, and you don't have any other comment boxes. Just change the MyName = Format(Target.Value, "##") to call your function that spells out the number. Can I have a copy of that function?

Please Login or Register  to view this content.

Thanks Mallycat,

That work around will do but does not meet all my requirements. The way EXCEL ( for that matter all Office applications ) pop up a context balloon ( eg when pointer moves over command button or a cell with comment ), can we not write a code to pop up a ballon at the tip of the pointer with a text and the event to execute this code could be mouse_over or Mouse_down?

You have asked for the copy of the function. This is fairly simple code which I wrote myself. Context is: in India we have other nomenclatures for numbers. We don't use million and billion. Instead we have Lac ( which is one tenth of a million ) and then there is Crore ( which is 10 millions ). My function handles numbers up to one hundred crores ( one billion ). Just to give you the idea,in India a number 123456789 will be Twelve Crore Thirt Four Lac Fifty Six Thousand Seven Hundred and Eighty Nine.

Here is the function which takes a number as argument and returns a string. I am not sure if it is of any use to you. One can easily modify the code to spell the number in million and billion by changing the values for B() and C() in the folowing code.


Function NS(NON) As String
If Not IsNumeric(NON) Then Exit Function
Dim A(100), B(5), C(5), D(10)
Dim NoSpell, temp, xxx, yyy, NO
B(1) = 10000000: B(2) = 100000: B(3) = 1000: B(4) = 100: B(5) = 1
C(1) = " Crore": C(2) = " Lac": C(3) = " Thousand": C(4) = " Hundred"
A(1) = "One": A(11) = "Eleven"
A(2) = "Two": A(12) = "Twelve"
A(3) = "Three": A(13) = "Thirteen"
A(4) = "Four": A(14) = "Fourteen"
A(5) = "Five": A(15) = "Fifteen"
A(6) = "Six": A(16) = "Sixteen"
A(7) = "Seven": A(17) = "Seventeen"
A(8) = "Eight": A(18) = "Eighteen"
A(9) = "Nine": A(19) = "Nineteen"
A(10) = "Ten": A(20) = "Twenty"
D(1) = "Twenty "
D(2) = "Thirty "
D(3) = "Forty "
D(4) = "Fifty "
D(5) = "Sixty "
D(6) = "Seventy "
D(7) = "Eighty "
D(8) = "Ninety "
For n = 1 To 8: For m = 1 To 9: A(10 * (n + 1) + m) = D(n) & A(m): Next: Next
For n = 3 To 9: A(10 * n) = D(n - 1): Next
yyy = 0
NO = Abs(NON)
If NO > 99 Then yyy = 1
p = 1
If NO > 999999999 Then
NoSpell = "TOO BIG"
Else
Do While NO > 0
temp = Int(NO / B(p))
xxx = IIf(p > 4 And yyy = 1, " And", "")
If temp > 0 Then NoSpell = NoSpell & xxx & " " & A(temp) & C(p)
NO = NO Mod B(p)
p = p + 1
Loop
End If
NS = NoSpell

End Function

Originally Posted by davesexcel

This post may help you

http://www.excelforum.com/showthread...10#post1641610

Now, I have tried somethin else, with the macro recorder, created a balloon or cloud call out and pointed it at cell A1 , and then put a formula in the callout that refers to A1, when you are in the call out, just go to the formula bar and refer a cell, anyway here's the code that I recorded

Please Login or Register  to view this content.

now for example purposes I use the selection change event so these code could be activated, select A1 and the macro1 kicks in select A2 and the sheet deletes

Please Login or Register  to view this content.

Good luck !,I hope you are able to get some Ideas from this

Thnaks Dave. Let me first apologises for coming back so late but then my laptop had conked off and it took me some time to get it right.

This is good idea but does not meet all my requirments. The number could be in any cell. My problem is that when the number in a cell is very big and without separators ( such as commas), it is difiicult to see if it is a billion or it si 10 billions. I don't even want to select the cell. I would just like to move the pointer over the cell and then I should get the ballon poped up at the tip of the pointer.The way EXCEL ( for that matter all MS Office suits ) pop up a context ballon when the pointer moves over a command button or over a cell with comment, can we not write a code to pop up a balloon just at the tip of the mouse pointer? Aren't these events such as mouse_over or mouse_down ( not click) on a cell available to us? If EXCEL can pop up that balloon with comment text when we move the pointer on the cell, why can we not do it through our code?

A V Veerkar

Hi there, here is a code that will insert a comment box into a cell

Range("A1").ClearComments

Range("A1").AddComment
Range("A1").comment.Visible = False
Range("A1").comment.Text Text:=Range("B1").Text

You could use it in a worksheet module such as selection change, so the comment will always be updated,

I know how to make a comment box into a cloud callout, but I do not know how to do it with VBA


have your drawing toolbar visible
Right click on the cell and select edit comment, click on the comments frame and then select the draw button and select change autoshape,

maybe with this info, some code can be created

A little late I know but.......


This code will change a comment box in range A5 into a call out cloud, and use range B1 as text

Please Login or Register  to view this content.