0

I am working on an Excel spreadsheet that uses a currency other than my native and I'd like to see quick and convenient currency conversions, possibly in the status bar (like when you select multiple cells and you see AVG and SUM there) or in a tooltip, kind of like with comments. All methods I've seen so far use additional cells and add-ins such as this one.

Two questions:

  1. Is this possible in the first place? Does Excel have extensibility model that would allow this?
  2. Does such tool exist?

Thanks.

Improve this question
4
  • No, this does not occur natively. I mean you could make an on selection macro to do it, but that would be pretty clunky.
    – Raystafarian
    Aug 10, 2015 at 9:26
  • I know this is not built in. But does such macro / add-in exist? Can it be done? I.e., does Excel provide extensibility points to hook into the status bar and/or tooltips?
    – Borek Bernard
    Aug 10, 2015 at 9:30
  • Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed.
    – bwDraco
    Aug 16, 2015 at 14:50
  • There has been an excellent answer already, thanks.
    – Borek Bernard
    Aug 16, 2015 at 18:53

1 Answer 1

Reset to default
1

You can put any text or number on the status bar with VBA.

Copy this on VBA to the sheet:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    q = Target.Value
    qRow = Target.Row
    qColumn = Target.Column
    If IsNumeric(q) Then
        m = moneyconverter(q)
        Application.StatusBar = m & " €"
    Else
        Application.StatusBar = False
    End If
End Sub

Public Function moneyconverter(originalmoney As Variant)
    moneyconverter = (originalmoney / 3)
End Function

There are two parts:

  • Worksheet_SelectionChange: Here is the detection of the selected cell.
  • moneyconverter function: here you have to put the currency conversion. For demonstrations purposes I assumed it was the original currency divided by 3 and the currency units were Euros (€).

The values will appear on the left side of the status bar and will be there even if you change to another opened workbook. To clear the value, in the original sheet select a text cell.

Improve this answer
7
  • Excellent! Is there a way to have this behavior on all sheets without the need to copy/paste to every sheet? (Sorry I never really worked with VBA.)
    – Borek Bernard
    Aug 10, 2015 at 14:40
  • @Borek I've modified the code to make it work for every sheet. You have to delete the old one and copy the new one to ThisWorkbook on VBA.
    – jcbermu
    Aug 10, 2015 at 14:50
  • @Borek On the line Application.StatusBar = m & " €" change the €symbol to anything you want / need.
    – jcbermu
    Aug 10, 2015 at 14:54
  • It works for me only if I go to Sheet1 -> View code and paste it, not when I paste it to ThisWorkbook -> View code.
    – Borek Bernard
    Aug 10, 2015 at 15:07
  • 1
    Updated it a little bit to keep the standard status bar for zero values and to respond to worksheet switching. See this Gist.
    – Borek Bernard
    Aug 11, 2015 at 10:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .