Forcing all boxes in Userform to have an entry otherwise data cannot be sent

[RPT_22]

Hi
I have a Userform to send data. The Userform has 10 entry boxes [ComboBox & Textbox]
I have filled in the Properties for each box on the Userform - TAB INDEX from 0 [first box] to 9 [last box], set TAB STOP to true and have message in the TAG sections

Currently the Userform will send the data whether i have 1 box only with an entry or if all the boxes have any entry

I want the value to be entered in order starting with the first box. If an entry is made out of order then the message in the TAG will force the next box in order to be filled in

I want the Userform to only send the data if all boxes have an entry but i cannot get my code to work.

Any help appreciated

Thanks

Private Sub UserForm_Initialize()
Me.Combobox1_Date.RowSource = ""
Me.Combobox1_Date.List = Application.Transpose(Sheets("Lists").Range("E3:E5").Value)
End Sub

Private Sub CommandButton1_Click()

Dim iRow As Long
Dim strDate As String
Dim wb As Workbook
Set wb = Workbooks.Open("\\xxx\xxxxx\xxxxx\xxxxxx\xxxxxxxxx\xxxxxx\xxxxxxxx.xlsx")
Dim ws As Worksheet
Set ws = wb.Worksheets("xxxxxx xxxxxx")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
 'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString And ctl.Enabled Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: Exit Sub
        End If
    Next
    
ws.Cells(iRow, 2).Resize(, 10).Value = Array(Me.Combobox1_Date.Value, TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, TextBox6.Value, ComboBox2.Value, TextBox7.Value, TextBox8.Value)

      'Clear all fields
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
          
    Next
    
   MsgBox "Data Transferred"
  
   wb.Close savechanges:=True
  
    End Sub


Private Sub CommandButton2_Click()
TextBox1.Value = Split(Now())(1)
End Sub

Private Sub CommandButton3_Click()
TextBox2.Value = Split(Now())(1)
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If MsgBox("Are you sure you want to close?", vbYesNo) = vbNo Then
    Cancel = True
End If
End Sub

 

[dmt32]

I changed the line of code and see attached image of list
Thanks

just noticed I placed the array index counter in wrong place

For Each Ctrl In ControlsArr
        With Ctrl
            If Len(.Value) = 0 Then
                If Len(msg) = 0 Then .SetFocus
                msg = msg & Ctrl.Name & Chr(10)
                'msg = msg & Me.Controls("Label" & i).Caption & Chr(10)
                .BackColor = vbYellow
            Else
                arr(i) = .Value
                .BackColor = vbWhite
            End If
        End With
        i = i + 1
    Next Ctrl

move variable i shown in BOLD to where shown above.

Dave

 

[RPT_22]

Thank you

 

[dmt32]

Thank you

welcome glad we were able to help & appreciate feedback

Dave

 

[mikerickson]

Mike where do you place this section of code in the VBA code inserted at the beginning of the thread
Thanks

That code would go in the Change event for each of the controls that need to have values.