Programmatically Add Min/Max to Status Bar in Bottom Right Corner in Excel

Question

I'd like to manipulate the status bar in Excel via VBA. I want to add Min/Max and perhaps more manipulations, but for now, that is the goal. I tried using the Macro Recorder, but it didn't pick anything up. To do it manually you just right click in the status bar and then select "Max/Min" and it shows. Is there a hidden way to adjust this via VBA?

Note: I'm using Office 2013 x32, but I have many end users who might be using newer versions such as Office 365 and/or x64 version of Excel, but nothing older

I think your best bet is just displaying these values via `Application.StatusBar` — dwirony, Jun 18 '21 at 22:35
Make you vba check for updates and update itself as necessary - that’s how we did it with users across 4 countries. Something simple like the vba running checks its own serial number against a server held value - if the server value is different the call update routine. — Solar Mike, Jun 19 '21 at 06:10
I am curious as to why you want a status/progress bar unless it is in a userform. What I have observed is, while macro is running, due to constant screen updating none of the messages in status bar is readable. — Charlie, Jun 29 '21 at 14:11
@dwirony My goal is to have this function in the status bar available for users to learn to use with all spreadsheets, not just display the results of a macro. I have seen a slight workaround using `worksheet_change` event to basically replicate the status bar vs calculating totals after a macro to display, but that's a lot of coding logic that Excel already has built in, just need to enable it programmatically. — FreeSoftwareServers, Jun 29 '21 at 20:09

Pᴇʜ · Accepted Answer · 2021-06-30T15:34:03.023

The values are set in the Windows Registry

HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\StatusBar\

Make sure 16.0 is replaced by the internal Office version number, that your users are using.

The registry keys are

MaxValue
MinValue

and they are of type REG_DWORD and 1 is enabled where 0 is disabled.

Note that after setting the registry keys you need to restart Excel no matter which of the following solutions you choose.

Solution 1: Group Policy

Set them via GPO (Group Policy) on your server and everyone has it enabled automatically with the next launch of Excel.

Solution 2: VBA

Alternatively save the registry keys using VBA and determine the version number you need for the registry path with Application.Version which returns 16.0 for Office 2019 Professional.
One method for registry manipulation can be found here: Read and write from/to registry in VBA (you will find other methods too if you google).

Solution 3: Batch File

You can also create the keys with a batch file:

reg add "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\StatusBar" /v MaxValue /t REG_DWORD /d 1
reg add "HKCU\SOFTWARE\Microsoft\Office\16.0\Excel\StatusBar" /v MinValue /t REG_DWORD /d 1

use reg /? for more info.

Solution 4: Powershell

And finally Powershell is possible too

set-itemproperty -path HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\StatusBar -name MaxValue -Value 1
set-itemproperty -path HKCU:\SOFTWARE\Microsoft\Office\16.0\Excel\StatusBar -name MinValue Value -Value 1

See Working with Registry Entries.

The Excel version can be read in Powershell with

$xl = New-Object -ComObject Excel.Application
$xl.Version
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Remove-Variable xl

by opening an Excel ComObject and asking for the version.

Way to tackle the challenge! I knew there had to be a way, hopefully this can be a resource for others. The challenge in goolging this question lies in the fact that VBA `Application.StatusBar` is a thing, but doesn't do what I wanted. Thanks! — FreeSoftwareServers, Jun 30 '21 at 22:23
I found `Application.Version` to be a great way to get the "Internal Version Number!" — FreeSoftwareServers, Jul 01 '21 at 00:35

FreeSoftwareServers · Answer 2 · 2021-07-01T20:47:07.017

Here is my solution using purely VBA. Thanks @PEH for his great detailed answer w/ many alternatives.

All you'd need to do is call SetMinMaxEnabledInExcelStatusBar module somehow.

Note: You could totally skip the SetRegKey and just SaveRegKey each run, I'm not sure which method I like better. I mostly used the SetRegkey as my testing module and then left it alone once it worked and I'm of the mindset why mess w/ registry if the "RegKeyCurrentStateInt = RegKeyDesiredStateInt"

Public Const DWordRegKeyEnabled As Integer = 1
Public Const DWordRegKeyDisabled As Integer = 0

Public RegKeyStr As String, RegKeyLocStr As String, RegKeyNameStr As String
Public RegKeyDesiredStateInt As Integer, RegKeyCurrentStateInt As Integer
Public RegKeyFoundBool As Boolean

Public Sub SetMinMaxEnabledInExcelStatusBar()

 RegKeyDesiredStateInt = DWordRegKeyEnabled
 
 RegKeyLocStr = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\" & Application.Version & "\Excel\StatusBar\"
 RegKeyNameStr = "MaxValue"
 RegKeyStr = RegKeyLocStr & RegKeyNameStr
 Debug.Print "RegKeyStr = " & RegKeyStr
 Call SetRegKey(RegKeyStr, RegKeyDesiredStateInt)
 RegKeyNameStr = "MinValue"
 RegKeyStr = RegKeyLocStr & RegKeyNameStr
 Debug.Print "RegKeyStr = " & RegKeyStr
 Call SetRegKey(RegKeyStr, RegKeyDesiredStateInt)
 
End Sub

Public Sub SetRegKey(RegKeyStr As String, RegKeyDesiredStateInt As Integer)
 
 RegKeyFoundBool = RegKeyExists(RegKeyStr)
 Debug.Print "RegKeyFoundBool = " & RegKeyFoundBool
 
 If RegKeyFoundBool = False Then
  Debug.Print "RegKeyFoundBool = False"
  Call SaveRegKey(RegKeyStr, RegKeyDesiredStateInt)
 Else
  Debug.Print "RegKeyFoundBool = True"
  
  RegKeyCurrentStateInt = ReadRegKeyVal(RegKeyStr)
  Debug.Print "RegKeyCurrentStateInt = " & RegKeyCurrentStateInt
 
  If RegKeyCurrentStateInt <> RegKeyDesiredStateInt Then
   Debug.Print "RegKeyCurrentStateInt <> RegKeyDesiredStateInt"
   Call SaveRegKey(RegKeyStr, RegKeyDesiredStateInt)
  Else
   Debug.Print "RegKeyCurrentStateInt = RegKeyDesiredStateInt"
  End If
 End If

End Sub

Public Function ReadRegKeyVal(RegKeyStr As String) As Integer
 ReadRegKeyVal = CreateObject("WScript.Shell").RegRead(RegKeyStr)
End Function

Public Function RegKeyExists(RegKeyStr As String) As Boolean

  On Error GoTo ErrorHandler
  CreateObject("WScript.Shell").RegRead (RegKeyStr)
  RegKeyExists = True
  Exit Function
  
ErrorHandler:
  RegKeyExists = False
End Function

Public Sub SaveRegKey(RegKeyStr As String, RegKeyDesiredStateInt As Integer, Optional RegKeyType As String = "REG_DWORD")
 CreateObject("WScript.Shell").RegWrite RegKeyStr, RegKeyDesiredStateInt, RegKeyType
 Debug.Print "Generated --> " & RegKeyStr & "," & RegKeyDesiredStateInt & "," & RegKeyType
End Sub

Programmatically Add Min/Max to Status Bar in Bottom Right Corner in Excel

2 Answers2

Solution 1: Group Policy

Solution 2: VBA

Solution 3: Batch File

Solution 4: Powershell