Search code examples
excelvbapowershellbatch-fileregistry

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

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

enter image description here

Solution

  • 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.