Data Validation List Problem
 

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

Data Validation List Problem
 

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.

"chacha" wrote:

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