Runtime error 1004 - method 'Range' of object '_Worksheet' failed

[stevieinselby]

Runtime error 1004 - method 'Range' of object '_Worksheet' failed
This seems to be a very common error message, but none of the other problems that people have been having with it seem to have the same cause as mine...

In the code for one of my worksheets I have:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target(1, 1).Address = "$Y$13" Then
    Cancel = True
    UserForm.Show
    End If
End Sub

When I double-click on Y13 in that worksheet, instead of bringing up the user form like it did a couple of weeks ago when I set it up, it now brings up the dreaded 1004, and when I debug, it highlights "UserForm.Show".

UserForm is all set up under Forms, and I can't figure out why it isn't running, especially as the bit it has highlighted refers to neither Range nor Worksheet. Any suggestions?

 

[Norie]

Sounds like the problem is in the userform code, probably in the Initialize or Activate event.

Goto Tools>Options... and check Break in class modules in the Error trapping level section on the General tab.

Now the next time you get the error Debug should lead you to the real culprit.

 

[stevieinselby]

Brilliant, thank you Norie ... that has led me straight to the named range that had a load of errors in it after I had deleted and moved stuff elsewhere. All fixed and fine now