Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
Hello Excel users and Experts,
Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
I don't know how to retrieve the value from this window directly without a
lot of API code at least. Maybe someone else knows something simpler. The popup commanbar that appears when you right click over the status bar is called "AutoCalculate". You can determine which control is selected by parsing its controls looking for the buttondown state. Having found the selected control, you can just use the selected control's index value to select the appropriate function from a list and run the function on the cell selection. You can also set which control you want to be selected using the Execute method:- Sub k() Dim i As Integer With Application.CommandBars("AutoCalculate") For i = 1 To .Controls.Count If .Controls(i).State = msoButtonDown Then MsgBox SBVal(i) Exit For End If Next '.Controls(7).Execute 'Selects the Sum button End With End Sub Function SBVal(mode As Integer) As Single Dim v As Single With Application Select Case mode Case 1 v = "" Case 2 v = .Average(Selection) Case 3 v = Selection.Count Case 4 v = .Count(Selection) Case 5 v = .Max(Selection) Case 6 v = .Min(Selection) Case 7 v = .Sum(Selection) End Select End With SBVal = v End Function Regards, Greg "L. Howard Kittle" wrote: Hello Excel users and Experts, Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
Here is an alternative
'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.DisplayStatusBar = True On Error Resume Next Application.StatusBar = "Average=" & Application.Average(Selection) & _ "; " & _ "Count=" & Application.CountA(Selection) & "; " & _ "Count nums=" & Application.Count(Selection) & _ "; " & _ "Sum=" & Application.Sum(Selection) & "; " & _ "Max=" & Application.Max(Selection) & "; " & _ "Min=" & Application.Min(Selection) End Sub -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "L. Howard Kittle" wrote in message . .. Hello Excel users and Experts, Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
Thanks Greg and Bob. Very interesting solutions. I appreciate the
responses. Regards, Howard "L. Howard Kittle" wrote in message . .. Hello Excel users and Experts, Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
Here is a link to some code I posted way back in 1998 that shows how to
change the selection (and restore it) http://tinyurl.com/2zzlve in case you wanted to loop through the various functions. -- Regards, Tom Ogilvy "L. Howard Kittle" wrote in message . .. Thanks Greg and Bob. Very interesting solutions. I appreciate the responses. Regards, Howard "L. Howard Kittle" wrote in message . .. Hello Excel users and Experts, Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Status Bar Function Results in VBA
Thanks Tom, I'll give it a go.
Regards, Howard "Tom Ogilvy" wrote in message ... Here is a link to some code I posted way back in 1998 that shows how to change the selection (and restore it) http://tinyurl.com/2zzlve in case you wanted to loop through the various functions. -- Regards, Tom Ogilvy "L. Howard Kittle" wrote in message . .. Thanks Greg and Bob. Very interesting solutions. I appreciate the responses. Regards, Howard "L. Howard Kittle" wrote in message . .. Hello Excel users and Experts, Excel 2003 Is there a way to display the worksheet status bar values of ADD, MAX, AVERAGE, et al, via VBA code. So if I select or reference a few cells with values in them, and have ADD selected in the status bar can I get a Msgbox or something to display that value? And can you change the function of the status bar via VB code. So you could present something like: "The SUM of the cells is " 88 "The AVERAGE of the cells is " 33 "The MAX of the cells is " 108 Help on STATUS BAR gives a True or False relevant to displaying the bar, but no help on displaying the value of the Status Bar. I have no final goal for this. I found a curious solution in the newsgroup that used keystrokes to SUM, AVG, etc. same colored formatted cells with the value displayed in the Status Bar. Wondered if I could reference the status bar value via code. I have written my own non-pro code to sum color coded cells on a sheet and I am aware of Dave McRitchie's true-pro code to do the same thing. Thanks Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't toggle function results display. | Excel Discussion (Misc queries) | |||
How do I get the concatenate function in Excel to display results? | Excel Worksheet Functions | |||
results display in filter function | Excel Worksheet Functions | |||
display count/results of filtering Excel in status bar | Excel Discussion (Misc queries) | |||
in excel how to display results of a function and text in the sam. | Excel Worksheet Functions |