Worksheet_SelectionChange "Compile Error: Invalid Use of Property"

[SoundLogic]

Good afternoon,

I am getting the error "Compile Error: Invalid Use of Property" whenever a selection change event occurs. I have tried to debug this for a few hours now and I'm simply frustrated

Any clues?

a little background...

I've done a lot of vba coding, but most of it has never been worksheet event based. This code was working then stopped working. It would work again once I restarted so I figured I was clicking something that would send it into a loop. I added the event handler code and it has not worked, at all, since then. The goal is to use cells where nRow = 30 and nCol = 7 as a viewer window (I merged a bunch of cells into this one area) - so it will show the currently selected item. Eventually I want to do something like:

if user selects anything in column 9, load text from a plsql file and displays in the viewer area
if user selects specific cells in an instruction area, it loads information from another worksheet giving them more information on that item (I know I could also use comments for this, but it's not as fun)
if user selects anything else, simply add that item to the viewing area

Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'prevent Select event triggering again when we extend the selection below
  Application.EnableEvents = False
  On Error GoTo Handler
  Debug.Print Target.Row
  Debug.Print Target.Column
  Debug.Print Target.Address
  Dim nRow As Long
  Dim nCol As Long
  nRow = Target.Row
  nCol = Target.Column
  
  If Target.Row = 1 And Target.Column = 1 Then
    Target.Offset(0, 2).Select
  Else
    If Not IsEmpty(Worksheets("Query").Cells(nRow, nCol).Value) Then
        Worksheets("Query").Cells(30, 7).Value = Worksheets("Query").Cells(nRow, nCol).Value
    Else
    End If
  End If
  
  Application.EnableEvents = True
Handler:
    Application.EnableEvents = True
    Stop
    Err.Source
End Sub

Any help would be greatly appreciated!!

 

[Hermanito]

Change this

Err.Source

into this

Debug.Print Err.Source

Also, I think you mean to write this, now you specify the same worksheet as source and target...

Worksheets("[COLOR=red]NotQuery[/COLOR]").Cells(30, 7).Value = _
Worksheets("Query").Cells(nRow, nCol).Value

Generally, it's better to fully qualify the objects you are using. Since each workbook has a worksheets collection, your code could start doing strange things when another workbook becomes active somehow. Therefore, this would be better:

ThisWorkbook.Worksheets("NotQuery").Cells(30, 7).Value = _
ThisWorkbook.Worksheets("Query").Cells(nRow, nCol).Value

 

[VoG]

Hello and welcome to MrExcel.

This code with the error handler removed works fine for me - I've assumed that this is meant to operate on the Query sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'prevent Select event triggering again when we extend the selection below
  Application.EnableEvents = False
  Dim nRow As Long
  Dim nCol As Long
  nRow = Target.Row
  nCol = Target.Column
  If Target.Row = 1 And Target.Column = 1 Then
    Target.Offset(0, 2).Select
  Else
    If Not IsEmpty(Cells(nRow, nCol).Value) Then
        Cells(30, 7).Value = Cells(nRow, nCol).Value
    End If
  End If
  Application.EnableEvents = True
End Sub

 

[Norie]

You don't just use Err.Source on it's own, it returns/sets a string so you either need to print it, assign it to a variale, or assign something to it.

 

[SoundLogic]

Thanks for your responses. I have been moving so have not had a chance to reply.

Err.Source was a concatenated line of a message box that I accidentally deleted when I was doing the copy and paste

Query should be the name - to put this in a logical perspective, when I do the following:

Cells(30,7).Value = Cells(nRow, nCol)

I'm using the cell at coordinate (30,7) as a selection box --> it's a cell I've merged with a number of other cells so it creates a viewing window, of sorts --> once I get this up and running, I plan to allow users to enter a location into the cell such that the location will be the directory location of a pl/sql file, on event selection, I would do the following:

Dim filePath as string
filePath = Cells(nRow, nCol)
Cells(30,7).Value = ReadFromFile(filePath)
''ReadFromFile returns the file contents into a string therefore the currently selected query would then be returned into the user's viewing area which would be cell 30,7

this code still does not work for me, oddly enough I can get it to work in Excel 2007 without issue, but it needs to work in 2003

I also tried isolating the code and worksheet to make sure there were no conflicts with any other worksheet in the workbook. I'm still receiving the same error and I tried checking for updates, I think excel should be completely updated at this point

any ideas?

Thanks!

 

[SoundLogic]

Update:

VoG, I tried removing the error handling and it is working again, but considering that the err.source item was a typo, even with the correct code where I print error messages to a message box (as in the below example) I still receive the compiler issue. This handler code works in other macros I'm running...could there be a conflict with the event handler and error handling?
Thx!

Handler:
Application.EnableEvents = True
Stop
msgbox Err.Source
</pre>