number to word

annadinesh

Board Regular
Joined
Mar 1, 2017
Messages
105
HI

is it possible to see the number/amount in words if we hover mouse cursor on a number in excel as a comment

not in any cell, it will show as a comment box and when we move the cursor from the number the comment should vanish


thanks


dinesh saha
9932022569
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Assuming that in A2 you have the amount. In cell B2 put the formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">127</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IFERROR(HYPERLINK(NewEvento(A2)),"")</td></tr></table></td></tr></table>

Put the following code in a module:

Code:
Public Function NewEvento(rngCelda As Range)
  CreateObject("WScript.Shell").PopUp SpellNumber(rngCelda.Value), 1, "Amount", 64
End Function


Function SpellNumber(ByVal MyNumber)
  Dim Dollars, Cents, Temp
  Dim DecimalPlace, Count
  ReDim Place(9) As String
  Place(2) = " Thousand "
  Place(3) = " Million "
  Place(4) = " Billion "
  Place(5) = " Trillion "
  ' String representation of amount.
  MyNumber = Trim(Str(MyNumber))
  ' Position of decimal place 0 if none.
  DecimalPlace = InStr(MyNumber, ".")
  ' Convert cents and set MyNumber to dollar amount.
  If DecimalPlace > 0 Then
  Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
  MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  End If
  Count = 1
  Do While MyNumber <> ""
  Temp = GetHundreds(Right(MyNumber, 3))
  If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
  If Len(MyNumber) > 3 Then
  MyNumber = Left(MyNumber, Len(MyNumber) - 3)
  Else
  MyNumber = ""
  End If
  Count = Count + 1
  Loop
  Select Case Dollars
  Case ""
  Dollars = "No Dollars"
  Case "One"
  Dollars = "One Dollar"
  Case Else
  Dollars = Dollars & " Dollars"
  End Select
  Select Case Cents
  Case ""
  Cents = " and No Cents"
  Case "One"
  Cents = " and One Cent"
  Case Else
  Cents = " and " & Cents & " Cents"
  End Select
  SpellNumber = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
  Dim Result As String
  If Val(MyNumber) = 0 Then Exit Function
  MyNumber = Right("000" & MyNumber, 3)
  ' Convert the hundreds place.
  If Mid(MyNumber, 1, 1) <> "0" Then
  Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
  End If
  ' Convert the tens and ones place.
  If Mid(MyNumber, 2, 1) <> "0" Then
  Result = Result & GetTens(Mid(MyNumber, 2))
  Else
  Result = Result & GetDigit(Mid(MyNumber, 3))
  End If
  GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
  Dim Result As String
  Result = "" ' Null out the temporary function value.
  If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
  Select Case Val(TensText)
  Case 10: Result = "Ten"
  Case 11: Result = "Eleven"
  Case 12: Result = "Twelve"
  Case 13: Result = "Thirteen"
  Case 14: Result = "Fourteen"
  Case 15: Result = "Fifteen"
  Case 16: Result = "Sixteen"
  Case 17: Result = "Seventeen"
  Case 18: Result = "Eighteen"
  Case 19: Result = "Nineteen"
  Case Else
  End Select
  Else ' If value between 20-99...
  Select Case Val(Left(TensText, 1))
  Case 2: Result = "Twenty "
  Case 3: Result = "Thirty "
  Case 4: Result = "Forty "
  Case 5: Result = "Fifty "
  Case 6: Result = "Sixty "
  Case 7: Result = "Seventy "
  Case 8: Result = "Eighty "
  Case 9: Result = "Ninety "
  Case Else
  End Select
  Result = Result & GetDigit _
  (Right(TensText, 1)) ' Retrieve ones place.
  End If
  GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
  Select Case Val(Digit)
  Case 1: GetDigit = "One"
  Case 2: GetDigit = "Two"
  Case 3: GetDigit = "Three"
  Case 4: GetDigit = "Four"
  Case 5: GetDigit = "Five"
  Case 6: GetDigit = "Six"
  Case 7: GetDigit = "Seven"
  Case 8: GetDigit = "Eight"
  Case 9: GetDigit = "Nine"
  Case Else: GetDigit = ""
  End Select
End Function


When you mouse over cell B2, the message will appear with the amount in letters
 
Upvote 0
I didn't find much about the same cell, you'll have to investigate something about "tooltips on cell"
 
Upvote 0
Try putting Dante's function in a standard module, then put this on the VBA sheet page corresponding to the sheet where you want the comments:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Target.AddComment
    Target.Comment.Visible = False
    Target.Comment.Text Text:=SpellNumber(Target.Value)
End Sub
This will add a comment to any cell you select and display the amount. It won't remove the comment when you select another cell, although we could probably figure something out. You could just remove the comment indicators:

Code:
    Application.DisplayCommentIndicator = xlNoIndicator
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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