VBA: Double click choices from a range

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
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.

Code:
[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]
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
How about
Code:
   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

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
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.

Code:
[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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
I missed a bit
Code:
             End If
             [COLOR=#ff0000]Exit For[/COLOR]
          End If
       Next j
 
Last edited:

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
Thank you Fluff. That works wonderful.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,773
Messages
5,470,699
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top