Excel 2003 ListObjects Problem

[Frederick Chow]

Hi all,

I have a problem about adding in-cell validation at the Insert Row of a list
object at run time.

To replicate:

1. Prepare a simple list with five items, e.g. Apple, Pears, Orange,
Bananas, Grape, etc, and name the range as "Products".

2. Run the following code to generate a list for each product (these list
should be on a separate worksheet):

' ********************************
Sub GenListObj()
Dim rngProduct As Range
Dim rngGrid As Range
Dim lstProductDetails As ListObject
Dim OutputRow As Long, OutputCol As Byte
Set rngProduct = Range("Products")

OutputRow = 1
OutputCol = 4
For Each rngGrid In rngProduct.Cells
Cells(OutputRow, OutputCol) = rngGrid.Value
OutputRow = OutputRow + 1
Cells(OutputRow, OutputCol) = "Quantity"
Cells(OutputRow, OutputCol + 1) = "Amount"
Set lstProductDetails = ActiveSheet.ListObjects.Add(xlSrcRange, _
Range(Cells(OutputRow,
OutputCol), Cells(OutputRow, OutputCol + 1)), _
, xlYes)
With lstProductDetails
.Name = "lst" & Replace(rngGrid.Value, " ", "")
.ShowTotals = True
'*** Problematic code begin
With .InsertRowRange.Cells(1).Validation
.Add Type:=xlValidateList, Formula1:="=Products"
End With
'*** Problematic code end
End With

OutputRow = OutputRow + 3
Next
End Sub
'*****************************************************************

I found that if at runtime if the cell pointer is located on D4, E4, D8, E8,
D12, E12, D16, E16 or D20, E20, then an run time error message will occur.
Otherwise the list, together with the in-cell drop down list, will populate
with no problems. Why is that? Please advise how to change the code.

Frederick Chow
Hong Kong

 

[keepITcool]

try:

start of code...

OutputRow = 1
OutputCol = 4

With Cells(OutputRow + 1, OutputCol)
If Not .ListObject Is Nothing Then
.ListObject.Delete
End If
End With

rest of code...

 

[Frederick Chow]

Thanks for your reply, but why should I delete the listobject? All I wanted
to do is just to add a cell validation in the first column of the insert row
of a listobject.

Frederick Chow

 

[keepITcool]

hmm..
that's where your demo failed when run twice.

tested some more:
If a cell already had datavalidation you cant just ADD another one.
you must either modifyor delete the existing validation.