Change/cycle through cell values by double clicking

[bparson017]

Hello everyone,

Last night I asked a similar question and got an extremely helpful answer! But today, I have a different scenario. Would it be possible to double click on any cell within a range of cells (C2:C70, D2:D70, E2:E70, etc) that produces the following values and can cycle through them:

P --> Ab --> AE --> T --> T30 --> P --> Ab --> etc......

These represent attendance codes I would use for taking attendance in my classroom. If anyone could assist me with the VBA needed to make this happen, I would be extremely grateful. Thank you for your help

 

[Scott T]

You could just use data validation.

In data validation allow: list
In source put your list separated by comas like p,Ab,AE,T,T30
Then click OK
You will then have a drop down list to pick from rather than having to click though a list to get to the one you want.

 

[bparson017]

Even though the list option that you have offered (Scott T) is totally a solution to my inquiry, I prefer the double click method because I use a tablet where the cycling isn't frequently used and all of the codes listed are in a descending order based on the frequency they appear. Also, these values commonly change as students arrive late or have documentation proving their reasoning for missing a class.

 

[Fazza]

how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Const sADDRESS_OF_INTEREST As String = "C2:E70"   
    
    If Not Intersect(Target, Range(sADDRESS_OF_INTEREST)) Is Nothing Then
        Cancel = True
        If Len(Target.Value) = 0 Then
            Target.Value2 = "P"
        Else
            Target.Value2 = Application.WorksheetFunction.Lookup(Target.Value2, [{"Ab", "AE", "P", "T", "T30"}], [{"AE", "T","Ab", "T30", "P"}])
        End If
    End If

End Sub