Runtime error 91

[Pobek]

I have a model that performs an automatic hide of a formular bar depending on what cells you pick (password entering cells and so on). There is a function that does this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("Tauthcod")) Is Nothing Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If
End If
End Sub

The model works and no problem. However when I send the file to someone else and that person tries to enable editing, i get the runtime error 91 saying "Object Variable or With block variable not set". When you debug, the second line (1st IF statement) gets highlighted.

Any quick and permanent fixes? ... silver-bullet anyone?

 

[Joe4]

I am not sure, but typically, the first line would look like this:

[COLOR=#333333]If Target.Count = 1 Then

instead of this:
[/COLOR]

If Selection.Count = 1 Then[/COLOR]

One typically uses "Target" and not "Selection" in Event Procedure code.

 

[Pobek]

Thanks Joe, that seems to have worked.

Can you briefly explain why that is please?

 

[Joe4]

By definition, Target is the range that was selected that triggers the Worksheet_SelectionChange event procedure code to run.
In Worksheet_Change event procedure code, Target is the range that was changed that triggers it to run.

Here is a good write-up on Event Procedures. Chip gets into Target a bit in the "Sample Event Procedure" section.
http://www.cpearson.com/excel/events.aspx

 

[Pobek]

By definition, Target is the range that was selected that triggers the Worksheet_SelectionChange event procedure code to run.
In Worksheet_Change event procedure code, Target is the range that was changed that triggers it to run.

Here is a good write-up on Event Procedures. Chip gets into Target a bit in the "Sample Event Procedure" section.
http://www.cpearson.com/excel/events.aspx

Thanks for that!! Ill have a look!