Worksheet Selection Change Event

[tyija1995]

Hi,

I have a worksheet with cell A5 displaying "CLICK ME TO SEE FILE PATH"

Then with the following code in the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Address = "$A$5" And Target.Value = "CLICK ME TO SEE FILE PATH" Then
        Target.Value = Application.ActiveWorkbook.FullName
    Else
        Cells(5, 1).Value = "CLICK ME TO SEE FILE PATH"
    End If
End Sub

The reason I added "On Error Resume Next" was because I was getting an error whenever I selected a cell with an error in it in the worksheet.

However with this added line of code it transforms my errors in to the file path.

How can I make it ignore the cells with errors in the worksheet?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CLICK ME TO SEE FILE PATH[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]

If I click cell A5 I will see the file path, if I click A6 I will also see the file path, which I don't want to happen

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Y:\Client0152\30625\Excel\0152MappingDoc.xlsm[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Y:\Client0152\30625\Excel\0152MappingDoc.xlsm[/TD]
[/TR]
</tbody>[/TABLE]


I want A6 to still show #DIV/0! after clicking on it as the cell A6 will now just display the file path at all times - A5 resets itself to "CLICK ME TO SEE FILE PATH" when not clicked so that's fine.

 

[jmacleary]

Hi there. A quick solution would be to change the code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo exiter
    If Target.Address = "$A$5" And Target.Value = "CLICK ME TO SEE FILE PATH" Then
        Target.Value = Application.ActiveWorkbook.FullName
    Else
        Cells(5, 1).Value = "CLICK ME TO SEE FILE PATH"
    End If
exiter:
End Sub

 

[John_w]

Use separate If statements:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$5" Then
        If Target.Value = "CLICK ME TO SEE FILE PATH" Then
            Target.Value = Application.ActiveWorkbook.FullName
        End If
    Else
        Cells(5, 1).Value = "CLICK ME TO SEE FILE PATH"
    End If
End Sub

 

[tyija1995]

Thank you both for the solutions, both worked a charm.

 

[jmacleary]

Youre welcome. BTW, I prefer John_w answer to mine.