Trigger Data Validation while data moves through VBA code

Question

I have two sheets: Sheet1 and Sheet2. Sheet 1 is the form for data entry by end user. While Sheet2 is the hidden worksheet that contains the data table formed using the data entered in the form. Sheet 1 has a SAVE button that triggers a VBA code to save the data from Sheet 1 form to Sheet 2 data table.

Sheet 2 has several data validation rules on each of the columns of the datatable. These data validations work fine and are triggered (and shows error messages) when I enter values directly in the data table manually in Sheet 2.

However, when I use this code to move the data from Sheet1 (Form) to Sheet2 (Table), the data validation is not checked and the value is pasted. When I move to the Sheet2, data validation rules are checked and highlights the cells which are not as per validation.

What I want is for excel to run those validations as soon as I use the code to transfer data from sheet1 to sheet2, and show those error messages for data validation failures.

And I also want to revert this saving to data table (i.e. delete this row from the table) if that is saved in the first place.

Code is:

private sub save_data()
    dim objList as ListObject
    dim form as Worksheet
    set objList = ThisWorkbook.Sheets("Sheet 2").ListObjects("Table1")
    set form = ThisWorkbook.Sheets("Sheet 1")
    dim newRow as ListRow
    set newRow = objList.ListRows.Add(AlwaysInsert:=True)
    with newRow.Range
        .Cells(1,1).Value = form.Range("C2").Value
    end with
end sub

I do not want to create new data validation using vba. I do not wish to create data validation rules through vba functions. I want to run those cell data validations while vba code runs. Any way to do this?

Please ask for more details / clarification if needed.

Conor12x Conor12x · Accepted Answer · 2021-05-20T07:03:34

Have you thought of using Access? It has the same functionality you're talking about built in. You can create forms to be filled out by the user which populates a table in the background.

Trigger Data Validation while data moves through VBA code

1 Answers