Validating in VBA
 

Hi

I want to only allow a list of names to be entered into column D. I
copy over the cells so the "excel, data, validation, list" doesn't
work. How do I create a code that only allows this list in the said
column?

CC


--
Casey C
------------------------------------------------------------------------
Casey C's Profile: http://www.excelforum.com/member.php...o&userid=29090
View this thread: http://www.excelforum.com/showthread...hreadid=488147

Validating in VBA
 

Lets put the list of valid entries in column B - about 100 valid names. Tehn:


Sub Macro1()
Dim i As Long
For i = 65536 To 1 Step -1
If IsEmpty(Cells(i, 4).Value) Then
Else
v = Cells(i, 4)
k = 0
For j = 1 To 100
If Cells(j, 2).Value = v Then
k = 1
End If
Next
If k = 0 Then
Cells(i, 4).Delete Shift:=xlUp
End If
End If
Next
End Sub

will scrub column D and remove all entries not appearing in column B.

--
Gary's Student


"Casey C" wrote:


Hi

I want to only allow a list of names to be entered into column D. I
copy over the cells so the "excel, data, validation, list" doesn't
work. How do I create a code that only allows this list in the said
column?

CC


--
Casey C
------------------------------------------------------------------------
Casey C's Profile: http://www.excelforum.com/member.php...o&userid=29090
View this thread: http://www.excelforum.com/showthread...hreadid=488147

Validating in VBA
 

I'd use another column that shows a warning message if it's invalid:

=if(d2="","",if(isnumber(match(d2,sheet2!a:a,0))," ","Not a match"))

And format that cell in big bold red letters. Then drag it down the column.

Casey C wrote:

Hi

I want to only allow a list of names to be entered into column D. I
copy over the cells so the "excel, data, validation, list" doesn't
work. How do I create a code that only allows this list in the said
column?

CC

--
Casey C
------------------------------------------------------------------------
Casey C's Profile: http://www.excelforum.com/member.php...o&userid=29090
View this thread: http://www.excelforum.com/showthread...hreadid=488147

--

Dave Peterson

Validating in VBA
 

Another way, via Worksheet_Change Event:

Assuming your list is in range B1:B20 and your working range is Column
D

Private Sub Worksheet_Change(ByVal c As Excel.Range)
If Intersect(c, Range("d1:d20")) Is Nothing Then Exit Sub
If Not Range("b1:b5").Find(c) Is Nothing Then
c = c.Value
Else
c.ClearContents
End If
End Sub


David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=488147

Validating in VBA
 

Correction: (as bolded)

Another way, via Worksheet_Change Event:

Assuming your list is in range *B1:B5* and your working range is
*D1:D20*

Private Sub Worksheet_Change(ByVal c As Excel.Range)
If Intersect(c, Range("d1:d20")) Is Nothing Then Exit Sub
If Not Range("b1:b5").Find(c) Is Nothing Then
c = c.Value
Else
c.ClearContents
End If
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=488147

Validating in VBA
 

Casey:

try something like this:

'----------------------------------------------------------------------
'TARGET is the cell you want to add validation to
'
'LISTRANGE would be the cells in column CC _
that have the data you want your TARGET to_
be limited to.
'----------------------------------------------------------------------

Sub AddValidation(Target As Range, ListRange As Range)

Dim strAddress As String

strAddress = "=" & ListRange.Address
With Target.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strAddress
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True

End With

End Sub

Validating in VBA
 

Casey: Try something like this...

'-----------------------------------------------------
'
'TARGET is the cell you want to add validation to
'
'LISTRANGE are the cells in column CC that contain _
the list of names that you want the TARGET _
to be limited to
'
'-----------------------------------------------------
Sub AddValidation(Target As Range, ListRange As Range)

Dim strAddress As String

strAddress = "=" & ListRange.Address
With Target.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strAddress

.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True

End With

End Sub

Validating in VBA
 

Casey, try this:

'-----------------------------------------------------
'
'TARGET is the cell you want to add validation to
'
'LISTRANGE are the cells in column CC that contain _
the list of names that you want the TARGET _
to be limited to
'
'-----------------------------------------------------
Sub AddValidation(Target As Range, ListRange As Range)

Dim strAddress As String

strAddress = "=" & ListRange.Address
With Target.Validation
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strAddress
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

End Sub