Hello everybody
I have an attachment which do a specific task .. It's a decreasing validation list ..
To do that task in Sheet1 I had to create a nother sheet named "Lists" and create a dynamic range named "MyList"
and use formulas to achieve that task
I want to do that decreasing validation list by code .. I don't want any helper columns and no other sheets
Just want my main List to be for example in Range("H1:H15") in Sheet1 and the decreasing list to be in Range("A1:A15")
When decreasing validation list in range("A1:A15") I want to keep the source list as it is , as I found a code but it removes the source list!!
Hope my request is clear
Convert formulas to vba codes



Re: Convert formulas to vba codes
Hi
Put the following into the Sheet1 code:
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngOriginalList As Range Dim rngTestValues As Range Dim cell As Range If Target.Column = 1 Then Set rngTestValues = Sheets("Sheet1").Range("A1:A15") Set rngOriginalList = Sheets("Lists").Range("A1:A15") 'Copy the original list to the next column over rngOriginalList.Copy rngOriginalList.Offset(0, 1) For Each cell In rngTestValues If Not cell = vbNullString Then 'It's not a blank cell Sheets("Lists").Range("B" & Application.Match(cell.Value, rngOriginalList.Offset(0, 1), 0)).Delete End If Next cell End If End Sub
And change the code for MyList to:
=OFFSET(Lists!$B$1,0,0,COUNTA(Lists!$B:$B),1)
That should do it.

Re: Convert formulas to vba codes
Thanks for swift reply
I applied your steps but the validation list doesn't work at all
I discovered that MyList named range is corrupted
After fixing it and trying to select an item from the list in A1:A15 in sheet1 , I noticed that the named range MyList corrupted againCan we do without this range and create it by code...?

Re: Convert formulas to vba codes
Try this code:
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count = 1 And Not Intersect(Target, Range("A1:A15")) Is Nothing Then Dim x() As String: ReDim x(0 To 14) As String For i = LBound(x) To UBound(x) If Not Evaluate("countif($A$1:$A$15,""Yasser" & i + 1 & """)>0") Then x(i) = "Yasser" & i + 1 Next i With Range("$A$1:$A$15").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=Replace(Join(x, ","), ",,", ",") End With End If End Sub

Re: Convert formulas to vba codes
Thanks Mr. S O for this pretty solution
But I think it will be suitable to these values only
These values Yasser1 and so on are just for example.. I don't want to imply specific values in the code..
I want simply to depend on main list for these values in Sheets("Sheet1").Range("H1:H15") for example and apply the validation list in Sheets("Sheet1").Range("A1:A15")...
Hope it is clear 

Re: Convert formulas to vba codes
Ah right okay then, much simpler:
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count = 1 And Not Intersect(Target, Range("A1:A15")) Is Nothing Then x = [H1:H15].Value For Each i In x If Not Evaluate("countif($A$1:$A$15,""" & i & """)>0") Then ii = ii & i & "," Next With Range("$A$1:$A$15").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=Left(ii, Len(ii)  1) End With End If End Sub

Re: Convert formulas to vba codes
Thank you very much Mr. S O
you are awesome fantastic.. I have all the day searching for such a great solution
I can not belive that I will sleep now
Thanks a lot for this fantastic presentHow to mark this thread as solved ? I clicked Thread tools but didn't find Mark as solved.!!

Re: Convert formulas to vba codes
Not a problem, I think only a moderator can close a thread however they are generally left open so that other people may contribute alternative/better solutions so don't worry about it.

Re: Convert formulas to vba codes
This is my other post at excelforum http://www.excelforum.com/excelprogramm…tml#post3957570

Re: Convert formulas to vba codes
Mr. S O
I want to use the source list again in the same sheet with a nother range say range("C1:C15")? Is that possible?
Each range should be isolated !!
Hope it is clear 

Re: Convert formulas to vba codes
Assuming you haven't posted this on other forums....
change "$A$1:$A$15" to the address of the range you want to apply the validation to. If the ranges are not contiguous then use a comma to separate the addresses.
e.g. "$A$1:$A$15, $C$1:$C$15"

Re: Convert formulas to vba codes
Thanks for reply
I didn't post that on other forums !!
The data validation after the change applied to both ranges .. But for range("A1:A15") the decreasing list is ok ..
But for the range("C1:C15") is not working well 
Re: Convert formulas to vba codes
You need to change all instances of "A1:A15" to the address you require.

Re: Convert formulas to vba codes
I tried this
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim X, I, II If Target.Cells.Count = 1 And Not Intersect(Target, Range("A1:A15,C1:C15")) Is Nothing Then X = Range("H1:H15").Value For Each I In X If Not Evaluate("countif($A$1:$A$15,""" & I & """)>0") Then II = II & I & "," 'If Not Evaluate("countif($C$1:$C$15,""" & I & """)>0") Then II = II & I & "," Next On Error Resume Next With Range("$A$1:$A$15,$C$1:$C$15").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Left(II, Len(II)  1) End With End If End Sub
Look I want to apply the decreasing list to both ranges , A1:A15 and C1:C15 as isolated ..
If I select Yasser1 and Yasser3 in range A1:A15 ,, these items will not be available again in range A1:A15
But they are now avaialble in C1:C15 and if selected in C1:C15 they disappeared from C1:C15
Hope it is clear now 
Re: Convert formulas to vba codes
Okay, I think I understand  try this:
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count = 1 And Not Intersect(Target, Range("A1:A15, C1:C15")) Is Nothing Then x = [H1:H15].Value For Each i In x If Not Evaluate("countif($A$1:$A$15,""" & i & """)>0") Then j = j & i & "," If Not Evaluate("countif($C$1:$C$15,""" & i & """)>0") Then k = k & i & "," Next With Range("$A$1:$A$15").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=Left(j, Len(j)  1) End With With Range("$C$1:$C$15").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:=Left(k, Len(k)  1) End With End If End Sub


Re: Convert formulas to vba codes
Thank you very much for this great help
I received a great help from a great and brilliant person 
Re: Convert formulas to vba codes
Hi
Not sure if this should be a new thread.
I'm interested in doing something similar with several validation lists for several columns, Eg column A, C and E take their validation list from columns G,H and I.
Can this VBA code be modified to accommodate this?
Triff 
Re: Convert formulas to vba codes
Hi
Can you please start your own thread and question 
Re: Convert formulas to vba codes
Will do thanks.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!