BeforeDoubleClick Event not working

A

Ayo

I have this BeforeDoubleClick event, below, that I am trying to run.
Everything was working fine yesterday now, it just jumps from the "If" to
the "End If" statements. I can't figure out what's going on.
Any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim errorWS As Worksheet, siteWS As Worksheet
Dim i As Integer, rw As Integer

Set siteWS = Worksheets("Site Milestone Dates")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row

If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
errorWS_lastRow Then
Application.EnableEvents = False
Application.ScreenUpdating = False

siteWS.Range("C4").Value = Target.Value
siteWS.Range("E4").Value = UCase(Target.Offset(0, 1).Value)

rw = 7
For i = 2 To 23 Step 2
siteWS.Range("E" & rw).Value = Target.Offset(0, i).Value
siteWS.Range("G" & rw).Value = Target.Offset(0, i + 1).Value
rw = rw + 2
Next i

rw = 7
For i = 24 To 45 Step 2
siteWS.Range("M" & rw).Value = Target.Offset(0, i).Value
siteWS.Range("O" & rw).Value = Target.Offset(0, i + 1).Value
rw = rw + 2
Next i

Application.EnableEvents = True
Application.ScreenUpdating = True
End If
siteWS.Select
End Sub
 
R

Ryan H

It seems to me your If...Then statement is setup wrong. I assume you are
wanting the code inside the If...Then statement to run if the user double
clicks any cell in Col.A that is greater that 4, right? If so, change your
If...Then statement from

If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
errorWS_lastRow Then

to this...

If Not Intersect(Target, errorWS.Range("A5:A" & errorWS_LastRow)) Is Nothing
Then

Plus, don't forget to declare your variables. errorWS_LastRow should be
declared as Long.

Plus, use this code to find you last cell with data. This will work for all
versions of Excel.

errorWS_LastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Hope this helps! If so, let me know, click "YES" below.
 
A

Ayo

Thanks Ryan. Works great so far.

Ryan H said:
It seems to me your If...Then statement is setup wrong. I assume you are
wanting the code inside the If...Then statement to run if the user double
clicks any cell in Col.A that is greater that 4, right? If so, change your
If...Then statement from

If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
errorWS_lastRow Then

to this...

If Not Intersect(Target, errorWS.Range("A5:A" & errorWS_LastRow)) Is Nothing
Then

Plus, don't forget to declare your variables. errorWS_LastRow should be
declared as Long.

Plus, use this code to find you last cell with data. This will work for all
versions of Excel.

errorWS_LastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Hope this helps! If so, let me know, click "YES" below.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top