User Profile
OriolFM
Copper Contributor
Joined Apr 23, 2021
User Widgets
Recent Discussions
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.1.2KViews0likes3Comments
Recent Blog Articles
No content to show