I have created a form that uses TextBox and ComboBox controls.
When I save the form I want to check the Values in the Controls against the DataTable they originate in (I make changes on the form).
This worked well when I only had TextBox Controls, but when I added the ComboBox Controls and tried to rewrite the SaveData sub I started running into problems.
I’m not proficient yet at vba, and I’ve been wrestling with this for a couple of days now.
The code to build the form works, but I’ll post it anyway, along with the code for the SaveData sub which is where I’m getting the error code.
I first wrote this code without the “If TypeName(Control) = “TxtBox”” and was getting an error that Excel could not locate the object and hightlited the ComboBox code line on the Save sub. This kind of made sense to me that as it was told to look for both a TextBox and ComboBox that it would error when it first came across a TextBox and said “I can’t find ComboBox”.
So I figured I had to tell it to look at each Control and determine if it was a Text or Combo then do what needed to be done, but that isn’t working as Excel is telling me I have a Next without a For – apparently caused by having the If Statements structured the way I do (guessing). I’ve tried including Else and ElseIf but those failed also.
I need someone more proficient than I am to tell me what I’m missing.
This is the code that builds the ComboBoxes on the form:

  'Check for SetUp and Error fields
            If SetUpFld = ActiveSheet.Cells(HeadRow, DataCol).Value Then  'Check For Setup Field and build ComboBox
                    Set LblField = .Controls.Add("Forms.Label.1", "LblBox" & DataCol)
                    With LblField
                        .Top = TopPos
                        .Width = 65
                        .Left = LeftPos
                        .BackStyle = fmBackStyleTransparent
                        .Font.Size = FontSize 'Dynamic Font Size
                        .Caption = ActiveSheet.Cells(HeadRow, DataCol).Value 'Set Header Label
                    End With
        
                    Set CboField = .Controls.Add("Forms.Combobox.1", "CboBox" & DataCol)
                    With CboField
                        .Value = ActiveSheet.Cells(ActRow, DataCol).Value 'Get SetUp Value
                        .RowSource = "TblSetUps"
                        .SpecialEffect = fmSpecialEffectSunken
                        .Top = TopPos
                        .Left = LeftPos + 65
                        .Width = 60
                        .Height = 20
                        .Font.Size = FontSize
                    End With
            Else    'If not SetUp or Error Field then look for data fields

This is the portion of the code on the Save sub that I’m struggling with. I’ve included the entire sub, but I’m struggling with the portion in Bold. That’s the code I can’t figure out how to structure properly.

    Sub SaveData()
Dim ActRow As Long, DataCol As Long, DetailRow As Long, StartCol As Long, EndCol As Long, DataRow As Long
Dim TxtField As msforms.TextBox
Dim CboField As msforms.ComboBox
Dim Cntrl As Control
Dim ShtNm As String
Dim DTbl As String





If SetupSht.Range("E5").Value = Empty Then Exit Sub
    DataRow = SetupSht.Range("E5").Value
    ShtNm = ActiveSheet.Name 'Active Sheet Name
    ActRow = ActiveCell.Row 'Active Row
    DetailRow = SetupSht.Range("G5:G34").find(ShtNm, , xlValues, xlWhole).Row
    DTbl = SetupSht.Range("M" & DetailRow).Value
    
    On Error Resume Next
    On Error GoTo 0
    If DetailRow = 0 Then
           MsgBox "Please make sure to setup this sheet in the Dynamic Userform Table in the Setup Sheet"
           Exit Sub
    End If

    StartCol = SetupSht.Range("I" & DetailRow).Value 'Start Col
    EndCol = SetupSht.Range("J" & DetailRow).Value 'End Column
    
    If StartCol = 0 Or EndCol = 0 Then
        MsgBox "Please make sure to add Start and Ending columns for this table within the Setup Sheet"
        Exit Sub
    End If
    
  **  For DataCol = StartCol To EndCol
       On Error GoTo NextCol 'Ignore errors for non-existging text Fields (such as pictures)
       If TypeName(Cntrl) = "TxtBox" Then
       Set TxtField = DynamicForm.Controls("TxtBox" & DataCol)
       If Sheets(DTbl).Cells(DataRow, DataCol).Value <> TxtField.Value Then Sheets(DTbl).Cells(DataRow, DataCol).Value = TxtField.Value
       If TypeName(Cntrl) = "CboBox" Then
       Set CboField = DynamicForm.Controls("CboBox" & DataCol)
       If Sheets(DTbl).Cells(DataRow, DataCol).Value <> CboField.Value Then Sheets(DTbl).Cells(DataRow, DataCol).Value = CboField.Value
NextCol:
    Next DataCol**

End Sub

As I mentioned, I tried nesting the if statements. I tried writing it without the TypeName statements originally, but that wouldn’t work. I’ve tried using ElseIf. I’ve tried using “and” after the “Set” statements so as to remove one of the If statement lines thinking that might be causing my “Next without For” error.

Also, There are 19 data columns (DataCol). The columns on the DataTable are the same name and in the same order on the Form. The Form builder looks at column 1, checks to see if it’s a picture, if it is it creates the appropriate msforms.image field. If the column is a “Notes” field, it creates the label, then creates the TextBox, if it is a SetUp or Error column it creates the label, then creates a ComboBox, if it is anything else it creates a label and then a TextBox. As I said, the form is working as intended. But I’m struggling to get the values from the form to compare and save to the values in the table.