Help with a macro: Reset depending data validation lists

%3CLINGO-SUB%20id%3D%22lingo-sub-2160364%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20macro%3A%20Reset%20depending%20data%20validation%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2160364%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20friends%20from%20the%20forum%2C%20my%20question%20is%20as%20follows%3A%3C%2FP%3E%3CP%3EI%20got%20a%20piece%20of%20Vba%20code%20from%20one%20of%20the%20interesting%20youtube%20videos%20of%20Leila%20Gharani.%3C%2FP%3E%3CP%3EIt%C2%B4s%20great%20but%20I%20need%20to%20extend%20its%20capabilities%20furthermore.%3C%2FP%3E%3CP%3EAfter%20tuning%20it%20up%20with%20my%20needs%2C%20what%20this%20macro%20does%20is%20resetting%20to%20blank%20value%20a%20serial%20of%20depending%20drop-down%20-data%20Validation-%20lists%20in%20a%20range%20and%20it%20does%20it%20with%20the%20event%26nbsp%3B%20-%26nbsp%3B%3CEM%3EChange(ByVal%20Target%20As%20Range)%20-%26nbsp%3B%3C%2FEM%3Eso%20when%20you%20change%20the%20value%20of%20the%20first%20Drop-down%20list%20(in%20G4)%20all%20the%20depending%20lists%20are%20reset%20to%20a%20blank%20value.%3C%2FP%3E%3CP%3EIt%20works%20great%2C%20since%20the%20first%20option%20of%20all%20drop-down%20lists%20is%20a%20blank%20cell%2C%20so%20%22%22%20value%20is%20amongst%20the%20allowed%20choices.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20thing%20is%20that%20in%20my%20case%2C%20I%C2%B4m%20trying%20yo%20implement%20it%20to%20a%20Table%3B%20%3CSTRONG%3Ethose%20drop-down%20lists%20are%20placed%20in%20a%20table%20(Ctr-T)%3C%2FSTRONG%3E%2C%20which%20means%20that%20they%20propagate%20downwards%20as%20you%20progress%20on%20populating%20the%20database%20table%20with%20every%20new%20record.%26nbsp%3B%20Illustrated%20in%20the%20under%20picture%20with%20the%20example%20of%20the%206th%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RCanet_3-1614112000021.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257176i0A3CE3C617FE6106%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RCanet_3-1614112000021.png%22%20alt%3D%22RCanet_3-1614112000021.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20did%20with%20the%20validation%20formulas%2C%20I%C2%B4m%20now%20trying%20to%20implement%20the%20code%20against%20the%20first%20row%20of%20the%20table%20(4th)%20with%20the%20aim%20it%20propagates%20downwards%20as%20the%20table%20grows%2C%20but%20so%20far%2C%20the%20reset%20effect%20only%20applyies%20to%20the%20fourth%20row.%3C%2FP%3E%3CP%3EAfter%20all%20I%C2%B4ve%20tried%2C%20expected%20resetting%20to%20blank%2C%20all%20lists%20at%20the%20same%20row%20when%20changing%20the%20first%20one%20at%20the%20G%20column%2C%20does%20not%20happen.%20It%20keeps%20only%20happening%20on%20the%20first%20row%20only%2C%20where%20the%20code%20points%20to.%3C%2FP%3E%3CP%3EMacro%C2%B4s%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3EIf%20Target.Address%20%3D%20%22%24G%244%22%20Then%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3ERange(%22H4%3Ak4%22).Value%20%3D%20%22%22%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3EEnd%20If%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23808080%22%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20Tryied%20with%20The%20%23%20simbol%20to%20make%20it%20understand%20it%20is%20a%20table%2C%20but%20no%20success%20so%20far.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EThanks%20a%20lot.%26nbsp%3B%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ELink%20to%20the%20video%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DwWasYHG1lmM%26amp%3Bab_channel%3DLeilaGharani%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DwWasYHG1lmM%26amp%3Bab_channel%3DLeilaGharani%3C%2FA%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2160364%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Frequent Visitor

Hi friends from the forum, my question is as follows:

I got a piece of Vba code from one of the interesting youtube videos of Leila Gharani.

It´s great but I need to extend its capabilities furthermore.

After tuning it up with my needs, what this macro does is resetting to blank value a serial of depending drop-down -data Validation- lists in a range and it does it with the event  - Change(ByVal Target As Range) - so when you change the value of the first Drop-down list (in G4) all the depending lists are reset to a blank value.

It works great, since the first option of all drop-down lists is a blank cell, so "" value is amongst the allowed choices.   

The thing is that in my case, I´m trying yo implement it to a Table; those drop-down lists are placed in a table (Ctr-T), which means that they propagate downwards as you progress on populating the database table with every new record.  Illustrated in the under picture with the example of the 6th row. 

RCanet_3-1614112000021.png

 

As I did with the validation formulas, I´m now trying to implement the code against the first row of the table (4th) with the aim it propagates downwards as the table grows, but so far, the reset effect only applyies to the fourth row.

After all I´ve tried, expected resetting to blank, all lists at the same row when changing the first one at the G column, does not happen. It keeps only happening on the first row only, where the code points to.

Macro´s code:

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Range("H4:k4").Value = ""

End If
End Sub

 

I Tryied with The # simbol to make it understand it is a table, but no success so far.

Thanks a lot.  

 

Link to the video 

https://www.youtube.com/watch?v=wWasYHG1lmM&ab_channel=LeilaGharani

Learn how to reset a dependent drop-down list in Excel with a little bit of VBA. The problem we get with dependent data validation lists is that when you cha...
0 Replies