Results 1 to 4 of 4
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Error on opening in Protected View

    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 bulletin which 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

    error.png

    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, ActiveSheet.Columns("B:B")) Is Nothing Then
        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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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).
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •