PDA

View Full Version : [SOLVED] Method Intersect Error



dodonohoe
01-14-2014, 03:23 AM
I have the following code which inserts a signature once a specified cell range is activated. This works fine but once I switch between worsheets using hyperlinks it throws the error "Method Intersect of Object_Global failed". I have the code pasted into the worksheet where the signatures go. I can switch between worksheets without issue but if I click on a hyperlink which I need) to bring me to another worksheet in the same workbook I get the error. I have marked within the code where the debugger brings me.


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("C2:D18")
'ERROR is here
If Not Intersect(ActiveCell, Target) Is Nothing Then
signature
End If
End Sub

Sub signature()
Dim user1 As String
Dim date1 As Date
'Sheets("Checklist").Select
'If Trim(Cells(2, 3)) = "" Then
If ActiveCell = "" Then
user1 = Environ("UserName")
date1 = Now
ActiveCell = user1 & " @ " & date1
End If
End Sub

Many thanks,

Des

Aflatoon
01-14-2014, 04:09 AM
I don't really understand why you've written the SelectionChange code the way you have. Target represents the cell that has just been selected - you shouldn't try to change that. I suggest you use:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rMonitor As Range
Set rMonitor = Me.Range("C2:D18")

'ERROR is here
If Not Intersect(rMonitor, Target) Is Nothing Then


Signature



End If
End Sub

dodonohoe
01-14-2014, 05:09 AM
Hi Aflatoon,

That worked perfectly for me so thank you. I think that is about the 5th time you have helped me so I owe you a beer. I am marking this as solved.

All the best,

Des

Aflatoon
01-14-2014, 05:13 AM
You're welcome. One day, I'll hold you to that beer. ;)