VBA Fill Data To Another Column

I will try to be as understandable as possible. So thing is that I have a classic form for data input which has 19 columns. Depending on complexity of the project, sometimes we have additional steps which needs to be performed, for my userform that means additional column. I have created the add button for those kind of situations, in order to add additional column in specific place in table. Now problem comes with entering data in my table, as every textbox has its own predetermined place (column) where needs to store data, but when I add column it consequentially store data in wrong column. My question is, is it possible to make if statement, that will recognize that I have added additional column and that will according to that store the rest of the data to correct place. I was also thinking, if that's easier to fill the data based on the names of my headers.

Sub Submit()

Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("PFU")

If frmForm.txtRowNumber.value = "" Then
    iRow = [Counta(PFU!A:A)] + 2
Else
    iRow = frmForm.txtRowNumber.value
End If

With sh
    .Cells(iRow, 1) = iRow - 2
    .Cells(iRow, 2) = frmForm.txtID.value
    .Cells(iRow, 3) = frmForm.txtName.value
    .Cells(iRow, 4) = frmForm.cmbNatureIPR.value
    .Cells(iRow, 5) = frmForm.cmbStatus.value
    .Cells(iRow, 6) = frmForm.txtCity.value
    .Cells(iRow, 7) = frmForm.txtCountry.value
    .Cells(iRow, 8) = frmForm.txtGrantDate.value
    .Cells(iRow, 9) = frmForm.txtGrantNumber.value
    .Cells(iRow, 10) = frmForm.txtAgent.value
    .Cells(iRow, 11) = frmForm.txtCurrentOwner.value
    .Cells(iRow, 12) = frmForm.txtTargetOwner.value
    .Cells(iRow, 13) = frmForm.cmbDVStatus.value
    .Cells(iRow, 14) = frmForm.txtVerifiedOwner.value
    .Cells(iRow, 15) = frmForm.txtVerifiedApp.value
    .Cells(iRow, 16) = frmForm.txtVerifiedGrant.value
    .Cells(iRow, 17) = frmForm.cmbVerifiedStatus.value
    .Cells(iRow, 18) = frmForm.cmbRecAction.value
    .Cells(iRow, 19) = frmForm.txtComment.value
End With

End Sub

This is my code for add button

Private Sub cmdAddStep_Click()

Dim Table As ListObject
Dim ws As Worksheet

Set ws = Worksheets("PFU")

  If MsgBox("Are you sure you want to add additional colum?", vbYesNo + vbQuestion, "Added") = vbYes 
 Then
Set Table = ws.ListObjects("Table4")
Table.ListColumns.Add 12
Table.HeaderRowRange(12) = "New header"
End If

Call Reset


End Sub

Solution

You do not say anything, even if I think you should... I do not have time to wait.

I prepared a solution, working in the next circumstances: You can insert how many columns you need, but you will NOT fill data in those columns as you do in your first code. The code can be adapted to also deal with such a situation. Not very complicated.

You must build a string linking, on its logic, the column header with form controls where from the value is used:

strHeaders = "HeaderB;2|HeaderC;3|HeaderD;4|HeaderE;5|HeaderF;6|HeaderG;7|HeaderH;8|HeaderI;9|HeaderJ;10"

The building logic is simple: the header is associated with the control tag, separated by ";". Each header is sepatated by "|". So you must allocate appropriate tags to the form controls (starting from 2).

Sub SafeInsertOnHeader()
   Dim ws As Worksheet, Table As ListObject, strHeaders As String, h As Long
   Dim arrH As Variant, arrInt As Variant, El As Variant, Ctrl As Control, iRow As Long
   Set ws = ThisWorkbook.Sheets("PFU")
   Set Table = ws.ListObjects("Table1")
   strHeaders = "HeaderB;2|HeaderC;3|HeaderD;4|HeaderE;5|HeaderF;6|HeaderG;7|HeaderH;8|HeaderI;9|HeaderJ;10"
   arrH = Split(strHeaders, "|")

    If frmForm.txtRowNumber.value = "" Then
        iRow = [Counta(PFU!A:A)] + 2
    Else
        iRow = frmForm.txtRowNumber.value
    End If

   ws.Cells(iRow, 1) = iRow - 2 'independent of a control value

   For h = 1 To Table.HeaderRowRange.Count 'iterare through the headers
        For Each El In arrH                'iterate through the array elements
            arrInt = Split(El, ";")        'create each element array (header and tag)
            If Table.HeaderRowRange.Columns(h) = arrInt(0) Then
                For Each Ctrl In frmForm.Controls
                   If Ctrl.Tag = arrInt(1) Then 'for the tag matching the array association
                        ws.Cells(iRow, h).value = Ctrl.value
                   End If
                Next
            End If
        Next
   Next h
End Sub

If you need to use the new inserted column, during column insertion code, the strHeaders string will be amended with the appropriate header;tag. The string will be saved in Registry and used each time by Registry reading.

The next approach is even simpler. You will directly use the header name as control tag:

Sub SafeInsertOnHeaderBis()
   Dim ws As Worksheet, Table As ListObject, strHeaders As String, h As Long
   Dim arrH As Variant, El As Variant, Ctrl As Control, iRow As Long
   Set ws = ThisWorkbook.Sheets("PFU")
   Set Table = ws.ListObjects(1) ' ws.ListObjects("Table1")
   strHeaders = "HeaderA|HeaderB|HeaderC|HeaderD|HeaderE|HeaderF|HeaderG|HeaderH|HeaderI|HeaderJ|HeaderK"
   arrH = Split(strHeaders, "|")
    'I used your way of last row determinig but it can be done a little better
    If frmForm.txtRowNumber.value = "" Then
        iRow = [Counta(PFU!A:A)] + 2
    Else
        iRow = frmForm.txtRowNumber.value
    End If

   'Each control will have like tag the header name!
   ws.Cells(iRow, 1) = iRow - 2

   For h = 1 To Table.HeaderRowRange.Count
        For Each El In arrH
            If Table.HeaderRowRange.Columns(h) = El Then
                For Each Ctrl In frmForm.Controls
                   If Ctrl.Tag = El Then
                        ws.Cells(iRow, h).value = Ctrl.value
                   End If
                Next
            End If
        Next
   Next h
End Sub

So, for, let us say, headerA you must select txtID form control, press F4, in order to open Properties window, scroll down until you see Tag property and fill its value with "headerA" string. Do the same association for all your pairs header/control name, save the form and enjoy using it inserting columns...