number to word

annadinesh

New Member
Joined
Mar 1, 2017
Messages
22
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,851
Office Version
2007
Platform
Windows
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
 

annadinesh

New Member
Joined
Mar 1, 2017
Messages
22
Thanks for the reply,

But i want the comment should show on the same cell where the number is
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,851
Office Version
2007
Platform
Windows
I didn't find much about the same cell, you'll have to investigate something about "tooltips on cell"
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,801
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
 

Forum statistics

Threads
1,078,238
Messages
5,339,014
Members
399,273
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top