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.
