I've been trying to come up with a way to display some calculations for the users of my workbook, but without having to take up space in the workbook itself. The solution I think I've figured out is to use the statusbar, and I think this might be a helpful idea for others to use. I saw an example where a single calculation was put into the status bar which is where I got the idea of putting multiple custom calculations into the bar. My calculations are going to be much more elaborate than in this example, but to keep things simple, this is what I have put into my Workbook code:
I really only want this to update when an item in a certain column is changed, but I'm not sure how to set that up.
Question 1: Would it be a bad idea to just let it update with every little change of the workbook?
Question 2: I get the following error, the first time this thing fires "User-defined type not defined", but then it works fine after that. What am I don't wrong that I get this error?
Question 3: The workbook I want this code in is created by a macro from another workbook. How do I have that macro put this code into the new workbook (either right as it is created, or ideally, at the end of the macro from the original workbook)? - Had a hard time searching for answers on this because I get so many hits on how to create a new workbook itself and I already know how to do that.
Thank you all for your help. You are all so helpful and I really appreciate it.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = "Custom Calc1: " & Application.Evaluate("=sum(T5:T8)") & _
" Custom Calc2: " & Application.Evaluate("=sum(T9:T12)") & " Custom Calc2: " & _
Application.Evaluate("=Average(T5:T12)")
End Sub
I really only want this to update when an item in a certain column is changed, but I'm not sure how to set that up.
Question 1: Would it be a bad idea to just let it update with every little change of the workbook?
Question 2: I get the following error, the first time this thing fires "User-defined type not defined", but then it works fine after that. What am I don't wrong that I get this error?
Question 3: The workbook I want this code in is created by a macro from another workbook. How do I have that macro put this code into the new workbook (either right as it is created, or ideally, at the end of the macro from the original workbook)? - Had a hard time searching for answers on this because I get so many hits on how to create a new workbook itself and I already know how to do that.
Thank you all for your help. You are all so helpful and I really appreciate it.