Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Macro to allow different clicks for different view zooms

    I've given up trying to find a solution so turn here as I'm a noob. The following code allows me to click back and forth to a different view. What I would like to do is create a third view. So for example on first click view is 80. Second click view is 100. third click view is 120 and forth click view is back to 80 and repeat cycle

    Sub ZoomInOut()
    Application.ScreenUpdating = False
    Sheets("Calculator").Unprotect Password:=""
    '
    'MACROS BY EXCELZOOM.COM
    If ActiveWindow.Zoom = 150 Then
    ActiveWindow.Zoom = 100
    Else
    ActiveWindow.Zoom = 150
    End If
    Sheets("Calculator").Protect Password:=""
    Application.ScreenUpdating = True 'Optional
    End Sub

    Thank you for any possible solution

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Curness,

    Here is some code you can add using a spin button. The sheet will zoom up/down in increments of 20 between 80 and 120 inclusive. Place a spin button on the worksheet and the following code in th woeksheet module.

    HTH,
    Maud

    Code:
    Private Sub SpinButton1_SpinDown()
    Application.ScreenUpdating = False
        Sheets("Sheet1").Unprotect Password:=""
        x = ActiveWindow.Zoom
        If x = 80 Then Exit Sub Else ActiveWindow.Zoom = x - 20
        Sheets("Sheet1").Protect Password:=""
        Application.ScreenUpdating = True 'Optional
    End Sub
    
    Private Sub SpinButton1_SpinUp()
    Application.ScreenUpdating = False
        Sheets("Sheet1").Unprotect Password:=""
        x = ActiveWindow.Zoom
        If x = 120 Then Exit Sub Else ActiveWindow.Zoom = x + 20
        Sheets("Sheet1").Protect Password:=""
        Application.ScreenUpdating = True 'Optional
    End Sub
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your quick response. That's great and does the job. Is there anyway this can be assigned to a normal button as I have a graphic smart art symbol for the spreadsheet I'm using.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Curness,

    Assign the following code to a button. It will cycle through the settings as you requested.

    In a standard module:
    Code:
    Sub ZoomInOut()
    Application.ScreenUpdating = False
    Dim x As Long, newx As Long
       Sheets("Sheet1").Unprotect Password:=""
       x = ActiveWindow.Zoom
       Select Case x
           Case 80
               newx = 100
           Case 100
               newx = 120
           Case Else
               newx = 80
       End Select
       ActiveWindow.Zoom = newx
       Sheets("Sheet1").Protect Password:=""
       Application.ScreenUpdating = True 'Optional
    End Sub
    BTW...you can add the following code to the ThisWorkbook module so you do not have to protect, run your code, then re-protect the sheet. The UserInterFaceOnly:=True parameter allows the code to function on a locked sheet

    ThisWorkbook module:
    Code:
    Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect UserInterFaceOnly:=True
    End Sub
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect - and thank you for the other tip - this is a great helpful website.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •