VBA Code for putting cell value in Status Bar

[realestated]

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]

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]

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]

Also, any idea how to make the number update every a change is made in excel. Sorry for being such a newb.

 

[Rick Rothstein]

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]

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]

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]

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]

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]

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!!