Run time error '91' object variable or with block variable not set error on opening file

[chriscorpion786]

Hi All,

I made a macro file with a worksheet selection change events, whenever i send it to my colleagues over the internet by mail, when they open it, they have to enable editing, after enabling editing it throws this run time error '91' object variable or with block variable not set , once you click END, the file works properly.
Is there any workaround to remove this error although I dont think my code is causing the error.
Below is my code:

>>> use code - tags <<<

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.DisplayAlerts = False
'this macro looksup the date and returns the tasks of that date
Dim rng As Range
Dim VL As String
Dim defaultdate As Date
Dim startdt As Date
Dim enddt As Date

Set rng = ThisWorkbook.Sheets("CALENDAR").Range("Days")

ActiveSheet.Unprotect Password:="VBA"
If Not Target.Cells.Count > 1 And Not Application.Intersect(rng, Target) Is Nothing Then

VL = Application.WorksheetFunction.VLookup(Target, ThisWorkbook.Sheets("Controls").Range("Lookupdates"), 6, False)
ThisWorkbook.Sheets("CALENDAR").Shapes("Taskforce").TextFrame2.TextRange.Text = VL

Else

ThisWorkbook.Sheets("CALENDAR").Shapes("Taskforce").TextFrame2.TextRange.Text = ""

End If

ActiveSheet.Protect Password:="VBA"
Application.DisplayAlerts = True

End Sub

 

[BobBridges]

I don't see an error off-hand...which doesn't prove anything . What statement is highlighted when the program throws the error?

 

[Marc L]

Hi,​

​

without the codeline raising the error it's just a guessing challenge to be posted on some mind readers forum but not here …​

​

At the first glance to the code I saw a very bad thing !​

Never use WorksheetFunction with any Excel function which may return an error as it crashes directly the procedure …​

Remove it - just keep Application before the function name - and allocate its result to a Variant variable​

and check if it's an error via IsError VBA function for example …​

​

 

[chriscorpion786]

I don't see an error off-hand...which doesn't prove anything . What statement is highlighted when the program throws the error?

Hi, thanks for replying, I removed the worksheetfunction as Marc L explained and now when I download it and enable editing it works fine.
Thank you,

 

[chriscorpion786]

Hi,​

​

without the codeline raising the error it's just a guessing challenge to be posted on some mind readers forum but not here …​

​

At the first glance to the code I saw a very bad thing !​

Never use WorksheetFunction with any Excel function which may return an error as it crashes directly the procedure …​

Remove it - just keep Application before the function name - and allocate its result to a Variant variable​

and check if it's an error via IsError VBA function for example …​

​

Hi, Your recommendation to remove the worksheetfunction works fine, I used application and now it dosen't throw an error.
Many thanks, learnt a lesson here.