Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why the DataValidation Code not working?
Hi All,
to my pre question I got the following code: What I intend to do is in my sheet2 range A1,a1.end (xldown) is named as "MemberList" and in my sheet1 B2 i have a data validation type List(Memberlist) if i type in sheet1 A2 the first few letters the data validation in B2 should list only names starting those typed letters in A2 since my list is so long browsing down and selecting makes this way easy. all my "MemberList" data are in Ucase any idea why the following code do not work? Private Sub Worksheet_Change(ByVal Target As Range) Dim cInput As Long, cValues As Long Dim i As Long, j As Long Dim vValue Const dvCell As String = "$B$2" Const selectCell As String = "$A$2" Const listSheet As String = "Sheet2" Application.EnableEvents = False If Target.Address = selectCell Then Worksheets("Sheet2").Columns(3).ClearContents cInput = Len(Target.Value) cValues = Worksheets(listSheet).Range ("MemberList").Cells(Rows.Count, _ 1).End(xlUp).Row j = 1 For i = 1 To cValues vValue = Worksheets(listSheet).Range ("MemberList").Cells(i, _ 1).Value If UCase(Left(vValue, cInput)) = UCase (Target.Value) Then Worksheets(listSheet).Cells(j, "C").Value = vValue j = j + 1 End If Next i End If If j 1 Then ThisWorkbook.Names.Add Name:="shortRange", RefersTo:="=" & listSheet _ & "!$C$1:$C$" & j - 1 With Range(dvCell).Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=shortRange" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range(dvCell).Value = Worksheets(listSheet).Range ("N1").Value Range(dvCell).Select End If Application.EnableEvents = True End Sub TIA Soniya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering or Formatting DataValidation List | Excel Discussion (Misc queries) | |||
DataValidation Check in cell of excel | Excel Discussion (Misc queries) | |||
datavalidation conditional list | Excel Discussion (Misc queries) | |||
datavalidation conditional list | Excel Discussion (Misc queries) | |||
Code not working | Excel Programming |