Excel VBA to select first value in Validation list

[Pancakes1032]

Hi All,

I'm trying to create a worksheet change event so that when the value is "In" on Column B the corresponding value in Column A will change to the first value in the data validation list.

The validation list in Column A is = "Alejandro,Not In"

The code works perfectly if Column B is "Out" It'll change the value in Column A to "Not In" but not when Column B is "In" I don't want to have to hand type every single employee's name in the code. Each individual cell of Column A has it's own validation list with first the employee's name and then Not In, as shown above.

This is the code I have right now, that isn't working properly:

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .count = 1 Then
            If .Row > 1 And .Column = 2 Then
                If .Value = "Out" Then
                    .Offset(0, -1).Value = "Not In"
                  End If
                  
                  ElseIf .Column = 3 Then
                If .Value = "Out" Then
                    .Offset(0, -2).Value = "Not In"
                    If Value = "In" Then
                    .Offset(0, -2)(.Validation.Formula1, 2).Cells(1, 1).Value
                  
                  End If

[SamT]

I don't want to have to hand type every single employee's name in the code

Never, ever encode business information, especially in an some application's hidden code such as Validation and Conditional Formatting code.
Never! Never! Never!






One of our greats said this and I can't repeat it enough.

"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H.
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H.
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H.
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H.
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H.

Literally, I can not repeat it enough to get it across to all our guests.

Basically WHAT you want is a way to tell everybody who is IN the office.

Specifically, WHAT you want is that if a cell in Column B changes to "In", then the cell in Column A changes to a particular value from a non existent list.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If Not (.Count = 1 And .Column = 2) Then Exit Sub

   If .Value = "Out" Then
       .Offset(0, -1) = "Not In"
   Else
       .Offset(0, -1) =Non-ExistentList(Unknown Reference Number)
   End If
End With
End Sub

Sorry, you need that list somewhere: A master Workbook, a Word Doc, an Access Table, an Outlook Address book. A text file... any where.

And we need to know how to relate the items on that list to the rows on this sheet. For that matter, how do the Users know which cell in Column B to change if the value in Col A is "Not In?"

A	B
John	In
Not In	Out
Not In	Out
Sue	In
Not In	Out

Bill just came in. Which Cell do I change to "In?"

[omp001]

... but not when Column B is "In" ...

This is the code I have right now, that isn't working properly:

Looking to your code it seems that this command is acting in the column "C", not in the column "B".
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim DV As String
    With Target
      If .Count > 1 Then Exit Sub
        If .Row > 1 And .Column = 2 Then
          If .Value = "Out" Then
            .Offset(0, -1).Value = "Not In"
          End If
        ElseIf .Column = 3 Then
          If .Value = "Out" Then
            .Offset(0, -2).Value = "Not In"
          ElseIf .Value = "In" Then
            DV = .Offset(0, -2).Validation.Formula1
            .Offset(0, -2).Value = Left(DV, Len(DV) - 7)
          End If
        End If
    End With
End Sub