Results 1 to 6 of 6

Thread: VBA: Double click choices from a range

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,011
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Double click choices from a range

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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Double click choices from a range

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,011
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Double click choices from a range

    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 i was already used. Also, A22 will hold the length of the range in column G.

    Code:
        Dim j As Long
        Dim jj   As Long: jj = Sheets("ControlPanel").Range("A22").Value
        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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Double click choices from a range

    I missed a bit
    Code:
                 End If
                 Exit For
              End If
           Next j
    Last edited by Fluff; Aug 3rd, 2019 at 09:47 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,011
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Double click choices from a range

    Thank you Fluff. That works wonderful.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Double click choices from a range

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •