CODEWITHSUNDEEP
excelvba
Nobelium
Nobelium

Reputation: 53

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

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?

Upvotes: 2

Views: 194

Answers (2)

D_Bester
D_Bester

Reputation: 5921

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

Upvotes: 1

Comintern
Comintern

Reputation: 22195

It's likely that at that state of the loading process for the workbook that the ActiveWindow hasn't been set yet (the _Global object isn't in a state where it can handle the request). The Window should still be available though - you just have to dig for it from the passed Range:

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:
    Me.Parent.Windows(1).Zoom = xZoom
End Sub

I would also suggest putting the same code in the Worksheet_Activate() event handler - IIR the Worksheet_SelectionChange won't fire if another sheet has focus when workbook opens and you tab to the one that you want to zoom.

Upvotes: 1

Related Questions