Select Case Error Problem

[bisel]

Hello All,

I am trying to use SELECT CASE to run a simple bit of VBA to change the zoom level of the worksheets in my workbook.

Here is the VBA ...

Private Sub ComboBox2_Change()
Dim wrksht As Worksheet


Select Case Sheet5.Range("mag_setting").Value
    Case Is = "95%"
        For Each wrksht In ThisWorkbook.Worksheets
        wrksht.Activate
        ActiveWindow.Zoom = 95 ' Set zoom level to 95% for each worksheet

    Case Is = "100%"
        For Each wrksht In ThisWorkbook.Worksheets
        wrksht.Activate
        ActiveWindow.Zoom = 100 ' Set zoom level to 100% for each worksheet

    Case Is = "110%"
        For Each wrksht In ThisWorkbook.Worksheets
        wrksht.Activate
        ActiveWindow.Zoom = 110 ' Set zoom level to 110% for each worksheet

    Case Is = "125%"
        For Each wrksht In ThisWorkbook.Worksheets
        wrksht.Activate
        ActiveWindow.Zoom = 125 ' Set zoom level to 125% for each worksheet

    Case Is = "150%"
        For Each wrksht In ThisWorkbook.Worksheets
        wrksht.Activate
        ActiveWindow.Zoom = 150 ' Set zoom level to 150% for each worksheet

End Select
    

Sheet16.Activate 'activate the home sheet
    
Application.ScreenUpdating = True

End Sub

But when I attempt to run the macro, I get this error message ...

Appreciate any help.

Regards,

Steve

 

[Cubist]

Try to remove the "Is =" in each case statement.

Private Sub ComboBox2_Change()
   Dim zoomLevel As Double
   Dim wrksht As Worksheet
 
    Select Case ComboBox2.Value
       Case "95%": zoomLevel = 95
       Case "100%": zoomLevel = 100
       Case "110%": zoomLevel = 110
       Case "125%": zoomLevel = 125
       Case "150%": zoomLevel = 150
       Case Else: Exit Sub
   End Select
 
    For Each wrksht In ThisWorkbook.Worksheets
       wrksht.Activate
       ActiveWindow.Zoom = zoomLevel
   Next wrksht
 
    Sheet16.Activate
 
    Application.ScreenUpdating = True
End Sub

 

[jolivanes]

For Each wrksht

needs a

Next

 

[Jaafar Tribak]

You don't need the Select Case statements or even iterating the worksheets:

Private Sub ComboBox2_Change()
    Application.ScreenUpdating = False
    ThisWorkbook.Worksheets.Select
    ActiveWindow.Zoom = CInt(Replace(Sheet5.Range("mag_setting").Value, "%", ""))
    Sheet16.Activate 'activate the home sheet
    Application.ScreenUpdating = True
End Sub

 

[jolivanes]

I was going to suggest similar after finding out what type of ComboBox it was.

 

[bisel]

Try to remove the "Is =" in each case statement.

Private Sub ComboBox2_Change()
   Dim zoomLevel As Double
   Dim wrksht As Worksheet
 
    Select Case ComboBox2.Value
       Case "95%": zoomLevel = 95
       Case "100%": zoomLevel = 100
       Case "110%": zoomLevel = 110
       Case "125%": zoomLevel = 125
       Case "150%": zoomLevel = 150
       Case Else: Exit Sub
   End Select
 
    For Each wrksht In ThisWorkbook.Worksheets
       wrksht.Activate
       ActiveWindow.Zoom = zoomLevel
   Next wrksht
 
    Sheet16.Activate
 
    Application.ScreenUpdating = True
End Sub

Thanks for help

 

[bisel]

Thanks for your help. Works great.

Steve