Error on opening in Protected View

Topic

New Reply

Hi,

Lately, I have been getting runtime errors thrown when downloading and opening excel files after clicking the enable button. There isn’t any recent internet buzz indicating the cause to be from an update but I did find an old MS bulletinwhich superficially describes a problem with the Workbook_Open event in Protected View. The description given is that the Workbook_Open event fires prior to the protected workbook being closed and transitioned to normal view.

The problems that I am seeing are not specifically related to the Workbook_Open event but rather to With statements referring to a sheet or my most recent episode:
If Not Intersect(Target, ActiveSheet.Columns(“B:B”)) Is Nothing Then

Notably, all my errors appear to be in the Worksheet_SelectionChange event

46090-error

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
‘—————————————
‘TOGGLE PRINT CHECKS (COLUMN P)
[COLOR=#0000ff]If Not Intersect(Target, ActiveSheet.Columns(“B:B”)) Is Nothing Then[/COLOR]
    CHECKS.Show
    Target.Offset(0, 1).Select
End If
If Not Intersect(Target, ActiveSheet.Columns(“P:P”)) Is Nothing Then
    If Target.Value = “” Then
        Target.Value = “a”
    Else:
        Target.Value = “”
    End If
    Target.Offset(0, -1).Select
End If
End Sub

As expected, after clearing the error once, the error does not return. Since I do not want to disable Protected View, I am looking for a solution. MS offers a workaround by delaying the execution of the Workbook_Open code by using WithEvents to test if the workbook is still in protected mode and redirecting it based on the results. I have yet attempt to adapt the code to the Worksheet_SelectionChange event but would rather stay clear of a workaround that adds extra code for those who may not be experiencing my issue and adds additional risk for failure.

I am very interested in anyone’s comments/experiences.

Thanks,
Maud

Reply | Quote

Replies

Hi Maud

..perhaps Excel hasn’t finished its own startup before the vba kicks in i.e. it doesn’t know what the activesheet object is yet. Is your Excel already loaded before the download?
..maybe your antivirus has detected Excel trying to open the file and is still scanning the file
..do you have a lot of add-ins that are still in the process of being loaded?
..does it matter if you try it on a faster PC?
..have you tried a DoEvents to force Excel to ‘catch its breath’

zeddy

Reply | Quote

zeddy,

Thanks for your insights however Excel boots and the file opens. It awaits the user to click the Protected View Enable button. No add-ins, Core I-5, Excel 13, always worked fine. These are my own created Excel files uploaded to WS then downloaded as a test to make sure all is OK. No code set to run on open.

Reply | Quote

The first thing I would suggest is removing the reference to ActiveSheet completely:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
'---------------------------------------
'TOGGLE PRINT CHECKS (COLUMN P)
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
    CHECKS.Show
    Target.Offset(0, 1).Select
End If
If Not Intersect(Target, Columns("P:P")) Is Nothing Then
    If Target.Value = "" Then
        Target.Value = "a"
    Else:
        Target.Value = ""
    End If
    Target.Offset(0, -1).Select
End If
End Sub

since it really shouldn’t be there anyway (as your sheet might not be active at the time).

Reply | Quote