Forum Discussion

OriolFM's avatar
OriolFM
Copper Contributor
Mar 13, 2023

Cells with data validation on a table will autoapply the same data validation to the headers

I have a big table with some columns that have data validation applied, to restrict the options in case someone edits the table manually.

 

I started using Excel VBA and Excel Forms to handle the data input in a more comfortable way. I have managed to add records to the table successfully, inserting new rows and copying the formulas.

 

The problem comes when I modify a cell that has data validation from Excel Forms/VBA. In those cases, the data validation is automatically applied to the whole column, INCLUDING the header. I'm using a function to target only the cell and the value I want to overwrite, so it shouldn't modify the header at all.

These are the functions I'm using to write to the table:

 

 

Public Sub WriteAEValue(ByVal id As String, ByVal column As String, ByVal content As Variant) 'escriure un valor a la taula
Dim cell As Range
Set cell = Application.Intersect(currentAERow, Range("ARTEFF_LIST").ListObject.ListColumns(column).Range)
cell.Value = content
End Sub

Public Sub setcurrentAE(row As Long)
Range("ARTEFF_LIST").ListObject.ListRows(row).Range.Select
Set currentAERow = Range("ARTEFF_LIST").ListObject.ListRows(row).Range
Call WriteAEValue(currentAE, "ARTEFF_ID", currentAE)
Call WriteAEValue(currentAE, "ARTEFF_TYPE", currentAEType)
'Range("TEMPLATES").Cells(row, 1).Value = currentPJ
currentAERowNumber = row
End Sub

 

 

I use setcurrentAE passing the row number to define a range inside the table, which is essentially the row I'm editing.

 

Then I use WriteAEValue to select the column I want from the table, and intersect

Any guesses to why this could happen?

UPDATE: 
I've realised something: the data validation issue does not appear when I'm adding rows or writing on the table, but rather when I quit the forms.

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    This:
    ListObject.ListRows(row).Range
    includes the heading cell. Use this instead:
    ListObject.ListRows(row).DataBodyRange

Resources