On Double Click - VBA

[eoinymc]

Hi,

I'm trying to write some code that allows me to double click on a particular cell and populate a cell in another sheet and bring me to that sheet. Just FYI, that will then trigger a whole chain of events.

But the problem I have is that I have a list and I don't know how long this list will be from month to month when the excel workbook has been updated.

I have a top 10 list, which I want to do the same thing to (i.e. click on a cell and populate another cell) and this is the code I use, which works great (but may not be the most efficient way of doing it):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Set Dashb = Sheets("Dashboard")
    Set TB = Sheets("Strong-Weak Routes")
    Set Ind = Sheets("Index")
    
    If Not Intersect(Target, TB.Range("E12")) Is Nothing Then
        If TB.Range("E12") <> "" Then
            Dashb.Range("E4") = TB.Range("E12").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E14")) Is Nothing Then
        If TB.Range("E14") <> "" Then
            Dashb.Range("E4") = TB.Range("E14").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E16")) Is Nothing Then
        If TB.Range("E16") <> "" Then
            Dashb.Range("E4") = TB.Range("E16").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E18")) Is Nothing Then
        If TB.Range("E18") <> "" Then
            Dashb.Range("E4") = TB.Range("E18").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E20")) Is Nothing Then
        If TB.Range("E20") <> "" Then
            Dashb.Range("E4") = TB.Range("E20").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E22")) Is Nothing Then
        If TB.Range("E22") <> "" Then
            Dashb.Range("E4") = TB.Range("E22").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E24")) Is Nothing Then
        If TB.Range("E24") <> "" Then
            Dashb.Range("E4") = TB.Range("E24").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E26")) Is Nothing Then
        If TB.Range("E26") <> "" Then
            Dashb.Range("E4") = TB.Range("E26").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E28")) Is Nothing Then
        If TB.Range("E28") <> "" Then
            Dashb.Range("E4") = TB.Range("E28").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    ElseIf Not Intersect(Target, TB.Range("E30")) Is Nothing Then
        If TB.Range("E30") <> "" Then
            Dashb.Range("E4") = TB.Range("E30").Value
            Dashb.Activate
            Dashb.Range("E4").Select
        End If
    End If
End Sub

So, I modified this code to create a variable and a for loop that (I thought) would mean I didn't have to define how long the list was and it would just pick it up, but it doesn't seem to be working. Here is the code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Set Dashb = Sheets("Dashboard")
    Set LT = Sheets("League Table")
    Set Ind = Sheets("Index")
    
    FRVar = Ind.Range("K98") + 13
    For i = 14 To FRVar
        If Not Intersect(Target, LT.Cells(FRVar, 5)) Is Nothing Then
            If LT.Cells(FRVar, 5) <> "" Then
                Dashb.Range("E4") = LT.Cells(FRVar, 5).Value
                Dashb.Activate
                Dashb.Range("E4").Select
            End If
        End If
    Next i
End Sub

Has anyone any ideas why this isn't working? I'm going to assume it's something to do with the for loop and not being able to use it.

Cheers,

Eoin

 

[CharlesChuckieCharles]

This appears as confusion between moving from one appraoch to another

The Target range in in LT

Why do you then loop through LT based on a counter created out of IND ??

Did you mean to say if target is in LT Range, then update DASHB E4 with value of Target

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim Dashb As Worksheet
    Dim LT As Worksheet
    Dim IND As Worksheet
    
    Set Dashb = Sheets("Dashboard")
    Set LT = Sheets("League Table")
    Set IND = Sheets("Index")
    
    If Intersect(Target, LT.Range("E14:E111")) Is Nothing Then '
        Exit Sub
    Else
                If Target.Value <> "" Then
                    Dashb.Range("E4") = Target.Value
                End If
    
    End If
Dashb.Activate
Dashb.Range("E4").Select

End Sub

 

[eoinymc]

Thanks Charles...

I was pointing to the IND sheet because it actually had the Final Row of the list in that particular cell..

Your code didn't seem to work for me, but it gave me what I needed to fix my code...here is the working code now!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set Dashb = Sheets("Dashboard")
    Set LT = Sheets("League Table")
    Set IND = Sheets("Index")
    
    FRVar = IND.Range("K98") + 13
    
    If Not Intersect(Target, LT.Range(LT.Cells(14, 5), LT.Cells(FRVar, 5))) Is Nothing Then
        Dashb.Range("E4") = Target.Value
        Dashb.Activate
        Dashb.Range("E4").Select
    End If


End Sub

Thanks again for your help.

Eoin