VBA - Resize Window dependent on selection

[surkdidat]

I have the following code, which should resize the window, dependent on the value in C16. However, it isnt working! Can anyone advise where its gone wrong please?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iMaxWidth As Integer
    Dim iMaxHeight As Integer
    Dim iStartX As Integer
    Dim iStartY As Integer
    Dim iDesiredWidth As Integer
    Dim iDesiredHeight As Integer
    Dim mType As String
    Dim trigger As Integer
    
    ActiveWindow.Zoom = 80
    mType = Range("C16").Value




        
        Select Case mType 'Set the window dimensions based on the Mtype
            Case "Standard"
                iDesiredWidth = 595
                iDesiredHeight = 382
            Case "Medium"
                iDesiredWidth = 595
                iDesiredHeight = 382
            Case "Deluxe"
                iDesiredWidth = 638
                iDesiredHeight = 392
                Columns("O").EntireColumn.Hidden = False
                Rows("18:19").EntireRow.Hidden = False
                
            Case "Super"
                Columns("O").EntireColumn.Hidden = False
                Rows("18:19").EntireRow.Hidden = False
                iDesiredWidth = 638
                iDesiredHeight = 392
            Case "Small"
                Columns("O").EntireColumn.Hidden = False
                Rows("18:19").EntireRow.Hidden = False
                iDesiredWidth = 638
                iDesiredHeight = 392
            Case "Super2"
                Columns("N:P").EntireColumn.Hidden = False
                Rows("18:20").EntireRow.Hidden = False
                iDesiredWidth = 690
                iDesiredHeight = 402
        End Select
        
        If mType <> "" Then 'A Mtype has been selected, reshape the window
            With Application
                '.WindowState = xlMaximized
                iMaxWidth = Application.Width
                iMaxHeight = Application.Height
                ' Adjust for starting point
                iMaxWidth = iMaxWidth - iStartX
                iMaxHeight = iMaxHeight - iStartY
                If iDesiredWidth > iMaxWidth Then
                    iDesiredWidth = iMaxWidth
                End If
                If iDesiredHeight > iMaxHeight Then
                    iDesiredHeight = iMaxHeight
                End If
                .WindowState = xlNormal
                .Width = iDesiredWidth
                .Height = iDesiredHeight
            End With
        End If
   
End Sub

 

[jim may]

Have you tried STEPPING-THRU the Code (Using the F8 Key). You will need to have Both windows (Data Sheet Window and VBA Window) side-by-side as you do so.