VBA Code for putting cell value in Status Bar

realestated

New Member
Joined
Jul 22, 2013
Messages
6
Hi,

I have been trying to put the value of a cell in Excel 2010 into the status bar so I do not have to flip back and forth between sheets after changing inputs in my model. Does anyone know how to alter the code below to make it automatically update and put it into the Accounting format?

Here is what I have so far:

Private Sub Worksheet_Calculate()
Application.StatusBar = "To DDA: " & Range("='Sources & Uses Detail'!H162").Value
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
Hi,

I have been trying to put the value of a cell in Excel 2010 into the status bar so I do not have to flip back and forth between sheets after changing inputs in my model. Does anyone know how to alter the code below to make it automatically update and put it into the Accounting format?

Here is what I have so far:

Private Sub Worksheet_Calculate()
Application.StatusBar = "To DDA: " & Range("='Sources & Uses Detail'!H162").Value
End Sub
Try it this way...

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
 

realestated

New Member
Joined
Jul 22, 2013
Messages
6
Try it this way...

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"


Rick,

The code you gave above is coming back with a compile error. Do you have any other suggestions? I have tried everything I can think of.


Thanks
 

realestated

New Member
Joined
Jul 22, 2013
Messages
6
Also, any idea how to make the number update every a change is made in excel. Sorry for being such a newb.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
Try it this way...

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Rick,

The code you gave above is coming back with a compile error. Do you have any other suggestions? I have tried everything I can think of.

Also, any idea how to make the number update every a change is made in excel. Sorry for being such a newb.
First off, the code line I posted was missing its last character... a closing parenthesis... sorry about that. This is what I should have posted....

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

By the way, since that equal sign is not part of the worksheet name, you do not need to include it as part of the cell reference... the code line will work with or without it being present. As for making the code work automatically, you need to tell us what is in cell H162 on your 'Sources & Uses Detail' sheet... a constant number or formula. If it is a formula, then you need to show that formula to us.
 

realestated

New Member
Joined
Jul 22, 2013
Messages
6
Try it this way...

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
I figured out the formatting part of this question with the following updates to the code:

Private Sub Worksheet_Calculate()
Dim toDDA As Double
Application.StatusBar = "To DDA: " & WorksheetFunction.Dollar(Range("=toDDA"))
End Sub


Any idea how to make the number in the status bar change when changes are made to the model?


Thanks!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
I figured out the formatting part of this question with the following updates to the code:

Private Sub Worksheet_Calculate()
Dim toDDA As Double
Application.StatusBar = "To DDA: " & WorksheetFunction.Dollar(Range("=toDDA"))
End Sub

Any idea how to make the number in the status bar change when changes are made to the model?
You probably do not need that equal sign in front of your Defined Name. As for making the Status Bar change automatically... you will need to answer the question I asked in Message #5 (your message and mine probably crossed each other while being posted).
 

realestated

New Member
Joined
Jul 22, 2013
Messages
6
First off, the code line I posted was missing its last character... a closing parenthesis... sorry about that. This is what I should have posted....

Application.StatusBar = "To DDA: " & Application.Text(Range("='Sources & Uses Detail'!H162").Value, "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

By the way, since that equal sign is not part of the worksheet name, you do not need to include it as part of the cell reference... the code line will work with or without it being present. As for making the code work automatically, you need to tell us what is in cell H162 on your 'Sources & Uses Detail' sheet... a constant number or formula. If it is a formula, then you need to show that formula to us.

Thanks Rick. Cell H162 is pulling from another sheet, that adds three cells from another sheet and adds them together. Those three cells are formulas that pull from other sheets. Do I need to do all of this in VBA, or can I just put the value of cell H162 in the Status Bar and have it update every time a variable is changed in the workbook? That is what it is doing in the workbook right now.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
Thanks Rick. Cell H162 is pulling from another sheet, that adds three cells from another sheet and adds them together. Those three cells are formulas that pull from other sheets. Do I need to do all of this in VBA, or can I just put the value of cell H162 in the Status Bar and have it update every time a variable is changed in the workbook? That is what it is doing in the workbook right now.
Wait a minute... I am confused... do you have what you asked for already working???? If not, try putting the code line for the assignment to the Status Bar in the SheetChange event procedure of the ThisWorkbook code module (not any individual Worksheet event module).
 

realestated

New Member
Joined
Jul 22, 2013
Messages
6
Wait a minute... I am confused... do you have what you asked for already working???? If not, try putting the code line for the assignment to the Status Bar in the SheetChange event procedure of the ThisWorkbook code module (not any individual Worksheet event module).

Simply solution. Thanks for the help!!
 

Forum statistics

Threads
1,081,421
Messages
5,358,584
Members
400,505
Latest member
JacquiT

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top