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]

Hi,
try following updates to your codes & see if changes do what you want

Option Base 1
Private Sub CommandButton1_Click()
    Dim ws          As Worksheet
    Dim wb          As Workbook
    Dim ControlsArr As Variant, arr() As Variant, ctrl As Variant
    Dim iRow        As Long, i As Long
    Dim strDate     As String, msg As String
    
    '--------------------------------------------------------------------------------------------
    '------------------------------------------SETTINGS------------------------------------------
    Const PathAndFileName As String = "\\xxx\xxxxx\xxxxx\xxxxxx\xxxxxxxxx\xxxxxx\xxxxxxxx.xlsx"
    
    Const wsDestination As String = "Sheet1"
    
    'change values as required
    '--------------------------------------------------------------------------------------------
    
    
    ControlsArr = Array(Combobox1_Date, TextBox1, TextBox2, TextBox3, TextBox4, _
                  TextBox5, TextBox6, ComboBox2, TextBox7, TextBox8)
    
    On Error GoTo myerror
    
    ReDim arr(1 To UBound(ControlsArr))
    i = 1
    'check user input
    For Each ctrl In ControlsArr
        With ctrl
            If Len(.Value) = 0 Then
                If Len(msg) = 0 Then .SetFocus
                msg = msg & ctrl.Name & Chr(10)
                .BackColor = vbYellow
            Else
                arr(i) = .Value
                .BackColor = vbWhite
                i = i + 1
            End If
        End With
    Next ctrl
    
    If Len(msg) > 0 Then
    
        MsgBox "Please Complete All Fields Highlighted" & Chr(10) & msg, 48, "Entry Required"
          
    Else
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(PathAndFileName, False, False)
        
        Set ws = wb.Worksheets(wsDestination)

        'find first empty row in database
        iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        
        ws.Cells(iRow, 2).Resize(, 10).Value = arr
        
        'Clear all fields
        For Each ctrl In ControlsArr
            ctrl.Value = ""
        Next
        Me.Combobox1_Date.SetFocus
    End If
    
    
myerror:
    If Not wb Is Nothing Then wb.Close Err = 0
    
    Application.ScreenUpdating = True
    If Err <> 0 Then
        MsgBox (Error(Err)), 48, "Error"
    Else
        
        If Len(msg) = 0 Then MsgBox "Data Transferred", 64, "Entry Complete"
    End If
    
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)
    Cancel = MsgBox("Are you sure you want to close?", vbYesNo, "Exit") = vbNo
End Sub

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

Dave

 

[RPT_22]

Thanks Dave....appreciated
Yes the update is very good.
Is it possible to change message box from displaying ComboBox/TextBox to the actual name of the missing info?
Thank you

 

[dmt32]

Thanks Dave....appreciated
Yes the update is very good.
Is it possible to change message box from displaying ComboBox/TextBox to the actual name of the missing info?
Thank you

quite possible - I used the name of the control as an example as did not know the names of your labels
Do the labels shown in your screenshot retain their default names e.g. Label1, Label2 etc or have you renamed them? I could hard code captions but better to read their values

Dave

 

[RPT_22]

quite possible - I used the name of the control as an example as did not know the names of your labels
Do the labels shown in your screenshot retain their default names e.g. Label1, Label2 etc or have you renamed them? I could hard code captions but better to read their values

Dave

Thank you

I have listed the names with corresponding Label #

Date = label1

Start Time = Label13

Finish Time = Label15

Start Pressure = Label15

Finish Pressure = Label16

Water Flush Temp = Label17

Result = Label19

Membrane Type = Label20

Comments = Label21

Supervisors Name = Label22

I could go through and re-label from 1 to 10

Thank you

 

[dmt32]

Thank you
I could go through and re-label from 1 to 10

That would be helpful then all you should need to do is replace this line

 msg = msg & Ctrl.Name & Chr(10)

with this

msg = msg & Me.Controls("Label" & i).Caption & Chr(10)

Dave

 

[RPT_22]

Labelled as below

Date = Label1

Start Time = Label2

Finish Time = Label3

Start Pressure = Label4

Finish Pressure = Label5

Water Flush Temp = Label6

Result = Label7

Membrane Type = Label8

Comments = Label9

Supervisors Name = Label10

 

[mikerickson]

You could add something like

CommandButton1.Enabled = True
For each oneControl in Array(TextBox1, TextBox2, ComboBox1, ...., TextBox10)
    CommandButton1.Enabled = CommandButton1.Enabled and (oneControl.Text <> vbNullString)
Next oneControl

To the Change event of each of the controls in question. That way the user couldn't press the button unless everything were filled.

 

[RPT_22]

That would be helpful then all you should need to do is replace this line

 msg = msg & Ctrl.Name & Chr(10)

with this

msg = msg & Me.Controls("Label" & i).Caption & Chr(10)

Dave

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

 

[RPT_22]

You could add something like

CommandButton1.Enabled = True
For each oneControl in Array(TextBox1, TextBox2, ComboBox1, ...., TextBox10)
    CommandButton1.Enabled = CommandButton1.Enabled and (oneControl.Text <> vbNullString)
Next oneControl

To the Change event of each of the controls in question. That way the user couldn't press the button unless everything were filled.

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