Excel Zoom

Problem

There are no keyboard shortcuts to zoom in/out with Excel

Solution

Use a custom VBA procedures to zoom in/out

VBA code

Sub MyZoomIn()
 ' Zoom in by 5%
 ' see also http://excelribbon.tips.net/T012582_Zooming_With_the_Keyboard.html
 Dim ZP As Integer
ZP = Application.WorksheetFunction.MRound(ActiveWindow.Zoom + 5, 5)
 If ZP > 400 Then ZP = 400
 ActiveWindow.Zoom = ZP
End Sub

Sub MyZoomOut()
 ' Zoom out by 5%
 ' see also http://excelribbon.tips.net/T012582_Zooming_With_the_Keyboard.html
 Dim ZP As Integer
 ZP = Application.WorksheetFunction.MRound(ActiveWindow.Zoom - 5, 5)
 If ZP < 10 Then ZP = 10
 ActiveWindow.Zoom = ZP
End Sub

Sub MyZoom100()
 ' see also http://excelribbon.tips.net/T012582_Zooming_With_the_Keyboard.html
 Dim ZP As Integer
 ZP = 100
 ActiveWindow.Zoom = ZP
End Sub

Calling the Subs

I initially assigned shortcut keys via the macro options dialogog. However, you are limited to Ctrl+ a letter and you cannot use Ctrl + « + » or Ctrl + « -« .

Assigning keys with Application.OnKey

Sub MyZoomAssignKeys()
 'Call MyZoomAssignKeys from ThisWorkbook at WorkBookOpen()
 'Ron de Bruin, Disable key or key combination or run a macro if you use it
 'How do I use Application.Onkey
 'https://www.rondebruin.nl/win/s4/win012.htm
 'http://www.msofficeforums.com/excel-programming/14804-application-onkey-numeric-plus.html
 
 'Shift key = "+" (plus sign)
 'Ctrl key = "^" (caret)
 'Alt key = "%" (percent sign)
 
 Application.StatusBar = "Assigning keys to MyZoom functions..."
 Application.OnKey "^{107}", "MyZoomIn" 'KeyPadPlus
 Application.OnKey "^{109}", "MyZoomOut" 'KeyPadMinus
 Application.OnKey "^{096}", "MyZoom100" 'KeyPad0
 Application.OnTime Now + TimeSerial(0, 0, 1), "MyZoomResetStatusbar"
 
End Sub
Sub MyZoomResetStatusbar()
 Application.StatusBar = False
End Sub

Calling MyZoomAssignKeys from PERSONAL.XLSB

Private Sub Workbook_Open()
 MyZoomAssignKeys
End Sub

Warning

Ctrl + and Ctrl – will no longer insert / remove rows  😉

See also

 

 

 

Publicités