Object Variable or With block variable not set when opening excel file

Nobelium Published at Dev

Nobelium

Because I couldn't find a way to increase font size of a dropdown list, I had to find a workaround. One option is to zoom in. Therefore I have this piece of code - which works perfectly. But as soon as I close the file and try to reopen it, there is an error message: "Object Variable or with block variable not set."

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo errorHandler

Dim xZoom As Long
xZoom = 60

If Target.Validation.Type = xlValidateList Then xZoom = 125

errorHandler:
ActiveWindow.Zoom = xZoom  'Debug highlights this row

End Sub

I event tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo errorHandler

Dim wb as Workbook

Dim xZoom As Long
xZoom = 60

Set wb = ThisWorkbook

wb.Activate

If Target.Validation.Type = xlValidateList Then xZoom = 125

errorHandler:
ActiveWindow.Zoom = xZoom  'Debug highlights this row

End Sub

But nothing seems to work... I simply don't know what the cause could be... Could anyone please help me?

D_Bester

Check that windows.count > 0 before doing your zoom.

If Application.Windows.Count > 0 Then
    ActiveWindow.Zoom = xZoom
End If

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error GoTo errorHandler

    Dim xZoom As Long
    xZoom = 60

    If Target.Validation.Type = xlValidateList Then
        xZoom = 125
        If Application.Windows.Count > 0 Then
            ActiveWindow.Zoom = xZoom  'Debug highlights this row
        End If
    End If
Exit Sub 'must exit before the error handler
errorHandler:
    Msgbox(Err.Description)
End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.