PDA

View Full Version : Excel VBA to select first value in Validation list



Pancakes1032
12-31-2014, 09:27 AM
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
12-31-2014, 12:10 PM
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. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT%28%29-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)

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
12-31-2014, 12:49 PM
... 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