MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

"Invalid use of Property" mystery

  Asked By: Thelma    Date: Jan 19    Category: MS Office    Views: 2002

My worksheet contains data as a result of applying an "Advanced Filter"

I wrote a vba event subroutine for "BeforeDoubleClick", intended to be
invoked upon double clicking any data cell.

Instead of getting the subroutine to work, excel freezes for several
seconds, and then responds with the message "Invalid Use of Property".

I have no idea what I did wrong. I couldn't trap the error
using "OnError" within my subroutine.



3 Answers Found

Answer #1    Answered By: Zoheth Mizrachi     Answered On: Jan 19

What is the code you've written?

Answer #2    Answered By: Blasa Fischer     Answered On: Jan 19

This is the code I wrote.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Dim row As Integer
Dim fname As String
Dim group As String
On Error GoTo error
If Cells(row, 2).Value = "Combo" Then
group = "CM"
group = "RS"
End If

row = Target.row

fname = ThisWorkbook.path & group & "/" & Cells(row, 3).Value & "/" & _
Cells(4, row) & "__" & Cells(5, row) & "__" & Cells(6, row) & ".txt"
FileName = fname
Exit Sub
error: // used for trying to trap  the error (couldn't trap)
Exit Sub
End Sub

Answer #3    Answered By: Estelle Pierce     Answered On: Jan 19

The culprit was found!
The expression: Cells(row, 2) was called with row=0.
For some obscure reason the error trapping started to work  only after I closed
excel and re-invoked it.

Didn't find what you were looking for? Find more on "Invalid use of Property" mystery Or get search suggestion and latest updates.