VBA: Double click choices from a range

[FryGirl]

I'm using a doubleclick event with the code below and all works well with the range of G2:G5.

At times the range could be longer, but not sure how to rearrange this to accomodate for say a range of G2:G10.

The range contains text.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    If Target.Address = "$H$6" Then
        If Target.Value = Sheets(MyCtrlPanel).Range("G2") Then
            Target.Value = Sheets(MyCtrlPanel).Range("G3")
        ElseIf Target.Value = Sheets(MyCtrlPanel).Range("G3") Then
            Target.Value = Sheets(MyCtrlPanel).Range("G4")
        ElseIf Target.Value = Sheets(MyCtrlPanel).Range("G4") Then
            Target.Value = Sheets(MyCtrlPanel).Range("G5")
        ElseIf Target.Value = Sheets(MyCtrlPanel).Range("G5") Then
            Target.Value = Sheets(MyCtrlPanel).Range("G2")
        End If
    End If
[/FONT]

 

[Fluff]

How about

   Dim i As Long

   If Target.Address = "$H$6" Then
      For i = 2 To 10
         If Target.Value = Sheets(MyCtrlPanel).Range("G" & i) Then
            If i < 10 Then
               Target.Value = Sheets(MyCtrlPanel).Range("G" & i + 1)
            Else
               Target.Value = Sheets(MyCtrlPanel).Range("G2")
            End If
         End If
      Next i
   End If

 

[FryGirl]

Hi Fluff and thank you for the help.

That seems to just cycle thru all the choices with one double click. I was looking for the user to cycle thru the choices one by one as they continue to double click or even just double click once for the next choice.

I had to change the variable to j since [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]i[/FONT] was already used. Also, A22 will hold the length of the range in column G.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim j As Long
    Dim jj   As Long: jj = Sheets("ControlPanel").Range("A22").Value[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    If Target.Address = "$H$6" Then
       For j = 2 To jj
          If Target.Value = Sheets(MyCtrlPanel).Range("G" & j) Then
             If j < jj Then
                Target.Value = Sheets(MyCtrlPanel).Range("G" & j + 1)
             Else
                Target.Value = Sheets(MyCtrlPanel).Range("G2")
             End If
          End If
       Next j
    End If[/FONT]

 

[Fluff]

I missed a bit

             End If
             [COLOR=#ff0000]Exit For[/COLOR]
          End If
       Next j

 

[FryGirl]

Thank you Fluff. That works wonderful.

 

[Fluff]

You're welcome & thanks for the feedback