Execute Code On Cell Selection

[Ark68]

Suppose I wanted to write code specific to a user selecting a cell on a worksheet, what would the process be? For example, I have a dynamic range of cells that I wish to apply a validation list to. However, for each cell, that validation list will be different. What I am thinking, and please correct me if there is a better option, that when the user clicks on that particular cell, the validation list is created and applied to that cell at that time.

Right now, I have created a generic validation list in advance, and applied it to all the cells in the range. This works, however, it may be a bit too generic in nature. It would be better suited for each cell to have it's unique validation list, but I think to do customize a validation list for all cells (of a dynamic range that may contain hundreds of different lists) in advance and applying each one individually could be daunting.

 

[offthelip]

I wrote some simple code that does what you want, but does depend on the validation lists being the same size. :
I put validation in A1 and A2 both of them looking at K1:K20 . then I put what I want for A1 validation in J1:J20 and A2 validation in L1:l20
then with this code in the worksheet code the validation changes depending on which row you select. Adding more columns after L you can go as far as you like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
trow = Target.Row
colno = trow + 11
inarr = Range(Cells(1, colno), Cells(20, colno))
Application.EnableEvents = False
Range(Cells(1, 11), Cells(20, 11)) = inarr
Application.EnableEvents = True
End Sub

 

[Ark68]

Thank you offthelip for your help. I'm just in the midst of adapting your suggestion and will give it a test.

 

[Ark68]

Hi all, here is my kick at the can.
My validation list is being created as expected when the user clicks on the cell, however, the validation isn't being applied? I'm expecting a dropdown but nothing.
Thoughts?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Stop
    If Not mbevents Then Exit Sub
    If Target.Value = "" Then Exit Sub
    mbevents = False
    ws_master.Unprotect
    On Error Resume Next
        If ActiveCell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            MsgBox "Active Cell does not have validation"
        Else
            MsgBox "Active cell has Validation"
        End If
    On Error GoTo 0
    ws_master.Protect
    trow = Target.Row
    crow = Target.Column
    
    If crow = 10 Then 'signature validation list (column j in master)
        With ws_thold
            .Range("I2:I20").Clear
            irw = 2
            lrw = .Cells(ws_thold.Rows.Count, "A").End(xlUp).Row 'last row of scheduled staff
            Set rng_stf_schd = .Range("A2:E" & lrw)
            bkg_dst = CDbl(ws_master.Range("M1")) + ws_master.Cells(trow, 6)
            
            'valid crew list (section 1 of 2 - appropriate)
            For L2 = 2 To lrw
                shift = .Cells(L2, 1)
                
                crw_st = bkg_date + .Cells(L2, 4)
                crw_ed = bkg_date + .Cells(L2, 5)
                If crw_ed < crw_st Then crw_ed = bkg_date + 1 + crw_ed
                svc_off = bkg_dst + TimeValue("00:45")
                Debug.Print "Shift: " & shift & "    svc_off: " & Format(svc_off, "h:mm AM/PM") & "    crw_st: " & Format(crw_st, "h:mm AM/PM")
                If crw_st < svc_off Then ' And crw_st > bkg_dst Then
                    .Cells(irw, 9) = shift
                    irw = irw + 1
                End If
            Next L2
        
            'spacer
            .Cells(irw, 9).Value = "NA"
            irw = irw + 1
            .Cells(irw, 9).Value = "NR"
            irw = irw + 1
            .Cells(irw, 9) = "-----"
            irw = irw + 1
        
           'invalid crew list (section 2 of 2 - inappropriate)
           For L2 = 2 To lrw
               shift = .Cells(L2, 1)
               crw_st = bkg_date + .Cells(L2, 4)
               crw_ed = bkg_date + .Cells(L2, 5)
               If crw_ed < crw_st Then crw_ed = bkg_date + 1 + crw_ed
               'If Not Round(svc_off, 3) >= Round(crw_st, 3) And Not Round(svc_off, 3) <= Round(crw_ed, 3) Then
               Debug.Print "Shift: " & shift & "    svc_off: " & Format(svc_off, "h:mm AM/PM") & "    crw_st: " & Format(crw_st, "h:mm AM/PM")
               If crw_st > svc_off Then
                   .Cells(irw, 9) = shift
                   irw = irw + 1
               End If
           Next L2
        
           '.Range("I2:I" & flcnt + 1).Value = .Range("A2:A" & flcnt + 1).Value
           
           Set rng_dsr_sig = .Range("I2:I" & irw)
           ThisWorkbook.Names.Add Name:="nr_dsr_sig", RefersTo:=rng_dsr_sig
           .Cells(trow, crow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Formula1:="=nr_dsr_sig"
        End With
    End If
End Sub

 

[Ark68]

I found my error and corrected it. I didn't include the worksheet object when I was assigning the validation to the cell. That problem has been overcome, but now dealing with another.

 

[offthelip]

Glad to have helped