Excel Already selected values from DropDown list dynamically updating on multiple sheets

N

nyuffancs

Joined
Feb 25, 2018
Messages
5
Reaction score
1
Hello,
after googling for 2 days and trying to figure it out myself and failing, i came here to ask for some help.

I have mulitple sheets with ranges in it containing values selected from a drop down list.
Worksheets("Part Database").Range("C3:C10") <-range contains values selected from DropDownList
Worksheets("APM2").Range("D5: D11") <-range contains values selected from DropDownList
Worksheets("APM3").Range("K8:K15") <-range contains values selected from DropDownList
Worksheets("Input").Range("I2:I7") <- this sheet range contains the DropDown list source, I1 is the header of the list, the code also pasted on this sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
For count_cells = 1 To Range("I1").CurrentRegion.Rows.Count - 1
Set rng = Worksheets("Part Database").Range("C3:C10")
If Intersect(Target, Range("I" & count_cells + 1)) Is Nothing Then
Else
Application.EnableEvents = False
new_value = Target.Value
Application.Undo
old_value = Target.Value
Target.Value = new_value
rng.Replace What:=old_value, Replacement:=new_value
Target.Select
End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

The following code updates the ALREADY selected values from the DropDown on the Part Database sheet, if i change the source of the DropDownList on sheet "Input". It works just fine.

But i want to add more two more sheet range to the code to work:
Worksheets("APM2").Range("D5: D11")
Worksheets("APM3").Range("K8:K15")

The question is, how do i implement it to the code, so it not only updates the Part Database sheet C3:C10 range, but the two more ranges on APM2 and APM3 sheets?

I have created an example workbook to make it easier to understand:
https://ufile.io/p2zfn
 
Hi,

Here is your code. I have appended last few lines only.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
For count_cells = 1 To Range("I1").CurrentRegion.Rows.Count - 1
Set rng = Worksheets("Part Database").Range("C3:C100")
If Intersect(Target, Range("I" & count_cells + 1)) Is Nothing Then
Else
Application.EnableEvents = False
new_value = Target.Value
Application.Undo
old_value = Target.Value
Target.Value = new_value
rng.Replace What:=old_value, Replacement:=new_value
Target.Select
End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
sh = 3
Do While sh <= Sheets.Count
Set rng = Sheets(sh).Range(Sheets(sh).UsedRange.Address)
rng.Replace What:=old_value, Replacement:=new_value
sh = sh + 1
Loop

End Sub


you can also make sure the cell you are writing the changes does contain the drop-down/data-validation..

Function ISDatavalidation(rng2 As Range) As Boolean

On Error Resume Next

DVtype = rng2.Validation.Type

On Error GoTo 0


If DVtype = 3 Then

ISDatavalidation = True

Else

ISDatavalidation = False

End If

End Function
 
Last edited:
Thank you for helping AmjiBhai!

Where do i exactly have to copy the second function code? Which sheet?
 
Just paste it in Worksheets("Input").
As soon as you make chages in the source of dropdown it should make corresponding changes in all the other sheets. To be on safe side make copy of your workbook. Try it and let me have feed back .
 
nyuffancs said:
Thank you for helping AmjiBhai!

Where do i exactly have to copy the second function code? Which sheet?
Click to expand...

You are welcome ...


The second function is intended to be in vba module...for this you just insert a new module and paste it there. The rest of the code should remain in the input sheet's view code area.

The Function to identify data validation dropdown cell ....is available for future use. In the current vba code none of the statement utilizing that function ...its been be provided just in case you may need that...Let me know if you need my help in that.
 
Sorry for my poor understanding, and the fact that im a newbie to this whole thing.

I pasted your code, it now works on all 3 sheets very well! But, now it changes everything in the whole workbook that contains the value of the source DropDownList, not only on the DataValidated cells. Can your Function ISDatavalidation get implemented in the code, so only the cells which contains DataValidation change?


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I2:I21")) Is Nothing Then

Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
Dim ws As Worksheet

Application.EnableEvents = False
new_value = Target.Value
Application.Undo
old_value = Target.Value
Target.Value = new_value

For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Replace What:=old_value, Replacement:=new_value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next

Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

This code also does the same as your code, it changes every value that it finds on the whole workbook if i change the source of the DropDown (Input sheet I2:I21 range). I face the same problem here again, it does change the already selected values, but if it finds another cell with value that matches what i change out of the DataValidated ranges, it also changes them.

For a better understanding, i made a quick vide(sorry for the quality) to try and help clarify the problem:
And again, thank you very much for helping!

I hae used your code in the video as you can see!

I uploaded the TEST workbook too:
https://ufile.io/y98sr
 
To simplify things a bit, i just want it to change ONLY the yellow highlighted ranges on sheet Part Database, APM1, APM2 if i change the source of the DropDownList. If its possible, and to spice the whole thing up, the source of the DropDown list is a dynamic range as i add more item to it.(In the TEST workbbok Input I2:I7 may change to I2:I8 if i add 1 more item to the list and so on). Sorry if its a bit complicated, and im certain that my explaining skills are way too low.
 
No complication at all . you have already simplified the solution by yellow-highlighting the target sells.

I am working on it, will be back shortly
 
Last edited:
I am unable to upload the file.I don't know how to do it with what file extension..therefore here is the copy of the revised code

Store this in your Module1
Function ISDatavalidation(rng2 As Range) As Boolean

On Error Resume Next

DVtype = rng2.Validation.Type

On Error GoTo 0


If DVtype = 3 Then

ISDatavalidation = True

Else

ISDatavalidation = False

End If

End Function

And Now in your Input sheet's view code area...copy the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
For count_cells = 1 To Range("I1").CurrentRegion.Rows.Count - 1
Set rng = Worksheets("Part Database").Range("C3:C100")
If Intersect(Target, Range("I" & count_cells + 1)) Is Nothing Then
Else
Application.EnableEvents = False
new_value = Target.Value
Application.Undo
old_value = Target.Value
Target.Value = new_value
rng.Replace What:=old_value, Replacement:=new_value
Target.Select
End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
sh = 3
Do While sh <= Sheets.Count
Set rng = Sheets(sh).UsedRange
cnt = 0

For Each cl In Sheets(sh).UsedRange
If ISDatavalidation(Sheets(sh).Range(cl.Address)) Then
cnt = cnt + 1
GoSub find_replace
End If
Next



sh = sh + 1
Loop

Exit Sub

find_replace:
Sheets(sh).Range(cl.Address).Replace What:=old_value, Replacement:=new_value
Return

End Sub


In the view code area of all the remaining sheets please make sure you don't have any code, keep those sheets without code,please.
 
Sorry for my late response, i was busy.

It now works perfectly, thank you for helping me out whith this one AmjiBhai!

Thank you so much!
 
Back
Top