0

I get the

Run-Time error 91: Object Variable or With block variable not set

when I open my program from a cloud storage location, and it opens with the warning question " "enable contents". When I click Enable the error 91 appears. If I open from my computer there is not problem. I do need to be able to download and open from the cloud storage location. My code is below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.ScreenUpdating = True

 With ActiveSheet
    .Cells.Interior.ColorIndex = xlNone
    If Target.Rows.Count = 1 Then
    Range("A" & Target.Row, "J" & Target.Row).Interior.ColorIndex = 27
    End If
End With

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
jceast7
  • 19
  • 7
  • a) Which line produces the error? b) Change `With ActiveSheet` into `With Me` and also specify `Me` for the `Range` like `Me.Range`. – Pᴇʜ Sep 23 '19 at 07:06
  • The error occurs on line 4 " .Cells.Interior.ColorIndex = xlNone" – jceast7 Sep 24 '19 at 11:40
  • Even after the changes I suggested? – Pᴇʜ Sep 24 '19 at 11:42
  • I replaced With Activesheet with With Me. Added Me.Range after With Me and received a Compile Error at .Range???? Changed to Me.Range("A" & Target.Row, "J" & Target.Row).Interior.ColorIndex = 27. It seems to work correctly. Thanks for the help. – jceast7 Sep 24 '19 at 12:03
  • wrote it as an answer so you can accept it as solved. – Pᴇʜ Sep 24 '19 at 12:25

1 Answers1

0

Change With ActiveSheet into With Me and also specify Me for the Range like Me.Range (or make it use the with statement and start it with a dot like .Range).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    Application.ScreenUpdating = True

    With Me
        .Cells.Interior.ColorIndex = xlNone
        If Target.Rows.Count = 1 Then
             .Range("A" & Target.Row, "J" & Target.Row).Interior.ColorIndex = 27
        End If
    End With    
End Sub

Why is this an important difference?

ActiveSheet is the sheet that has the focus (is on top) at the moment the code runs. But this is not necessarily the sheet that triggered the event or Target is in. Therfore you try to change the color of different sheet. Avoid using ActiveSheet at any cost. In most cases it is not needed (unless in rare cases eg if you write an addin or something similar).

You might benefit from reading How to avoid using Select in Excel VBA which is about a very similar topic.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73