• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto change of validated cell when source in data validation changed

Status
Not open for further replies.
T

Thomas Kuriakose

Active Member
Respected Sirs,

We have multiple data validation cells and these are linked to multiple sources lists. The source is bound to change and hence we need the validated cell to reflect the change as soon as the list is changed.

I found a code to get the validation cell change automatically for one reference. Here the validated cell is C2 and the lists are in range G2 to G9.

We have similar validated cells in C3,C4,C5 and the corresponding lists in H2 to J9.
Code: 
Option Explicit
Private oldValue As String
Private Const DVCell As String = "C2"
Private Const dataList As String = "G2:g9"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End If
End Sub

Kindly let me know how to get this working for multiple cells and lists.

Thank you very much,

with regards,
thomas
 

Attachments

  • Data Validation update.xlsm
    21.7 KB · Views: 38
Hi:

Do you need VBA for this? Can't this be achieved trough offset and named ranges? The offset formula on your uploaded workbook was wrong, I have corrected it and have put data validation on cell C2 you can replicate the same for other cells. If you are looking for something else come back with a more clear explanation.

Thanks
 

Attachments

  • Data Validation update.xlsm
    21.7 KB · Views: 48
Respected Sir,

Thank you very much for your support and for the correction in the offset formula.

Sir, I am not sure whether the solution can be achieved through formula.

(1) We have multiple data validation cells starting from C2 (in this sample C2 to C5) and the source of these cells validation are in cells G2 to J9 respectively.

C2 Data validation - source - G2 to G9
C3 Data validation - source - H2 to H9
C4 Data validation - source - I2 to I9
C5 Data validation - source - J2 to J9

The source values are subject to change periodically and we need the Data Validation cells to update the values automatically without going through the drop down.

Currently if you see, when the values in G2 to G9 changes, the value in C2 changes automatically based on the selection, for example current value in C2 is "pale", if we change the value in G2 to "apple", the value in C2 updates to "apple".

For this the above code was entered. This code is working for one cell data validation.

Kindly help with a solution for multiple data validation cells and having multiple source cells.

I hope I have explained the requirement.

Thank you very much,

with regards,
thomas
 
Not sure but this is how I read your problem.
Just get rid of all the existing code and paste the code on to worksheet code module
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Intersect(Target, Columns("g:j")) Is Nothing Then Exit Sub
    For i = 7 To 10
        With Cells(i - 5, "c").Validation
            .Delete
            .Add 3, Formula1:="=" & Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Address
        End With
    Next
End Sub
 
Respected Sir,

Thank you for your support on this query.

Sir, this did not work out.

The first code works for validated cell C2, when source in G2 is changed without using the drop down in C2.

I used the codes from the below post -
http://www.ozgrid.com/forum/showthread.php?t=139943

The above example works for one cell validation and one source list.

What we need is change when there are multiple validation cells and multiple source lists.

I hope I have explained correctly.

Thank you very much,

with regards,
thomas
 
Then I don't think what you are trying to do.

I thought validation in C2:C5 should be updated according to the change of List in G:J.
 
Respected Sir,

Thank you once again.

We have a situation where the List of values G:J for the validation are subject to change based on the status every month and the users need to manually select the data validation drop down to update the entries in C2 to C5.

The actual number of validated cells is twenty. The manual process of selection every time and change is creating errors.

With the above in mind, we wanted to get the changes in the list to be updated in the validated cells automatically.

Thank you very much,

with regards,
thomas
 
Hi Thomas,

See if this is what you are looking for...
In the validation itself I used a combination of offset and count to get a dynamic list of possible values.
Then wrote the following code to handle the automatic update:
Code: 
Dim OldValue, NewValue As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Columns("G:J")) Is Nothing Then
        OldValue = Target.Value
    End If
   
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
       
    Dim i As Integer
   
    For i = 2 To 5
        If Not Intersect(Target, Columns(i + 5)) Is Nothing Then
            NewValue = Target.Value
            If Cells(i, 3).Value = OldValue Then
                Cells(i, 3).Value = NewValue
            End If
        End If
    Next i
   
End Sub

Hope this helps.
 

Attachments

  • Data Validation update.xlsm
    16 KB · Views: 130
Respected Sir,

Amazing!! You nailed it.

This is exactly what we needed.

Thank you so much for the solution provided.

Sir, only one question, if the number of validated cells is 20, then we should copy the offset and count for each of the cells and for the code, kindly let me know which statements need to be altered.

Thank you very much once again.

with regards,
thomas
 
Thomas Kuriakose said:
Respected Sir,

Amazing!! You nailed it.

This is exactly what we needed.

Thank you so much for the solution provided.

Sir, only one question, if the number of validated cells is 20, then we should copy the offset and count for each of the cells and for the code, kindly let me know which statements need to be altered.

Thank you very much once again.

with regards,
thomas
Click to expand...
Don't mention it ;)

Now, for 20 cells...
You will need to have similar offset/count formula for each validated cell (you can copy/paste the formula and change the respective column)

About the code, you may or may not need to change it, depending on how the validated cells and respective lists are arranged.
If you can upload a new sample with the 20 cells and their lists I can take a look and adjust accordingly.

EDIT: Going over the code again, I realized that you will definitely need to make at least one change to the code for it to work on the 20 cells. I used "For i = 2 to 5" which means that if you have 20 cells (+16 below the current range), and assuming the lists are to the right of the current ones, this statement would need to be "For i = 2 to 21".
 
Last edited:
Status
Not open for further replies.
Back
Top