Data Validation List Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chacha - 01 Mar 2008 23:33 GMT

Hi everyone
My code bugs out, at my second validation list. This works if I do in the
spreadsheet without code, but it doesn't work with my code. If anyone can
suggest anything it would be great!
For i = 1 To nMembers
startPoint.Offset(i, 0).EntireRow.Insert
startPoint.Offset(i, 0) = i
startPoint.Offset(i, 3).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=_ xlBetween, Formula1:="=Sections"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
startPoint.Offset(i, 4).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween,
Formula1:="=INDIRECT(SUBSTITUTE($D$24,"""""""",""""""""))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next i

Reply to this Message

Joel - 02 Mar 2008 02:44 GMT

first I would try running the code without the insert row. Inserting a row
can really cause problems with the rest of the code. You can add the Insert
row at the end of the program after you perform the other operations

Also this code requires two validation lists on each row. One in an offset
of 3 and the other in an offset of 4 from startpoint. If you don't have a
validation in each row and each column you will fail the delete operation.

> Hi everyone
> My code bugs out, at my second validation list. This works if I do in the
[quoted text clipped - 34 lines]
> End With
> Next i

Reply to this Message