Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeDoubleClick Event not working
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeDoubleClick Event not working
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. -- Cheers, Ryan "Ayo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforeDoubleClick Event not working
Thanks Ryan. Works great so far.
"Ryan H" wrote: 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. -- Cheers, Ryan "Ayo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help With BeforeDoubleClick | Excel Programming | |||
BeforeDoubleClick | Excel Programming | |||
i want to know about "beforedoubleclick" event | Excel Programming | |||
BeforeDoubleClick Cancel=True not working | Excel Programming |