ActiveWorkbook.ActiveSheet.Name Run-time error 91 / Object variable not set

[rickincanada]

I am running the following code on one of my worksheets ("Deal Info" / Sheet3) however for some reason one of the users of this workbook is getting a Run-time error '91': Object variable or With block variable not set message when they open another workbook and this one is open. Here is the line where VBA takes me on debug:

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If ActiveWorkbook.ActiveSheet.Name = "Deal Info" Then
If ActiveSheet.Range("F25").Value <> OldVal Then
OldVal = Range("F25").Value
Call OptionFilter
ActiveWorkbook.Sheets("Deal Info").Select
End If
End If
End Sub

It highlights the "If ActiveWorkbook.ActiveSheet.Name = "Deal Info" Then" section. The strange thing is that I can't recreate the issue and none of the other 30+ people who use this workbook have complained about this issue. Any help here would be greatly appreciated!!!

 

[amazing_wonders]

Hi rickincanada, I am by no means an expert but I would like to help out when I can. I had a similiar situation one time. Try declaring your objects as variables.
Dim wk as Workbook
Dim ws as Worksheet
The use the variables in your code. VBA is kind of particular about using the worksheet objects, or at least with my limited experience it is. Maybe someone else can shed some light on the why's.
Let me know if this helped any at all.
Thanks

 

[rickincanada]

I'm not exactly sure where you would want me to use those variables once they've been declared...

Dim wk as Workbook
Dim ws as Worksheet

Set wk = ActiveWorkbook
Set ws = ActiveWorkbook.ActiveSheet

But then what do I do from here? I can't call wk.ws.Name. Thanks for your help!!

 

[amazing_wonders]

Maybe try this code. Your two IF statements can be combined into one.

<code>
Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Sheets("Deal Info").Range("F25").Value <> OldVal Then
OldVal = Range("F25").Value
Call OptionFilter
ActiveWorkbook.Sheets("Deal Info").Select
End If

End Sub

<code/>
Hope this is of some help

 

[rickincanada]

I just figured it out...

The OldVal = Range("F25").Value was placed inside of the IF statement and needed to be moved out and below the Static OldVal As Variant.

Thanks for your help amazing_wonders!

 

[amazing_wonders]

If you do that though, won't it be setting the current value of the cell to OldVal which in turn would never trigger your if statement. I would tend to think it would go at the end after the IF statement has ran. Just a thought.