CheckBox & Text Box with Multiple Functions

[LilMyers2010]

Below is the coding I have that currently functions almost flawlessly!

I need the Msg windows to send the user back to the form to finish entering the data required. As it is now the msg box will appear however when you click ok it just proceeds forward without even giving the option to add the data. Also when the checkbox is checked the tbAddress field autofills (this is working) but I need the tbCity, tbState, tbZip and tbEmail fields to not be required.

TIA

If tbName.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer Name"
tbName.SetFocus
End If


If tbAddress.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer Address"
tbAddress.SetFocus
End If




If lpu.Value = False And tbCity.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer City"
tbCity.SetFocus
End If








If lpu.Value = False And tbState.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer State"
tbState.SetFocus
End If






If lpu.Value = False And tbZip.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer Zip"
tbZip.SetFocus
End If






If lpu.Value = False And tbEmail.Text = "" Then
Cancel = 1
MsgBox "Please Enter Customer Email"
tbEmail.SetFocus
End If




Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Customer Database")


Dim lRow As Long
lRow = ws.Cells(1, 1).End(xlDown).Row + 1


ws.Cells(lRow, 1).Value = tbName
ws.Cells(lRow, 2).Value = tbAddress
ws.Cells(lRow, 3).Value = tbCity
ws.Cells(lRow, 4).Value = tbState
ws.Cells(lRow, 5).Value = tbZip
ws.Cells(lRow, 6).Value = tbEmail


tbName.Value = ""
tbAddress.Value = ""
tbCity.Value = ""
tbState.Value = ""
tbZip.Value = ""
tbEmail.Value = ""


lRow = lRow - 1


UserForm2.Hide
UserForm1.Show
End Sub

 

[dmt32]

Hi,
welcome to Forum

See if this update to your code does what you want

Option Base 1
Private Sub CommandButton1_Click()
    Dim arr() As Variant, ControlsArr As Variant
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Integer
    
'controls array
    ControlsArr = Array("tbName", "tbAddress", "tbCity", "tbState", "tbZip", "tbEmail")
    
    ReDim arr(1 To UBound(ControlsArr))
'loop each control
    For i = 1 To UBound(arr)
        With Me.Controls(ControlsArr(i))
'check for data entry
            If Len(.Value) = 0 Then
'check if all fields require entry
                If Me.lpu.Value And i < 3 Or Not Me.lpu.Value Then
'inform user
                    MsgBox "Please Enter Customer " & Choose(i, "Name", "Address", "City", "State", "ZIP", "Email"), 48, "Entry Required"
                    .SetFocus
                    Exit Sub
                End If
            Else
'store values to array
                arr(i) = .Value
            End If
        End With
    Next i
        
    Set ws = ThisWorkbook.Sheets("Customer Database")
'next blank row in range
    lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
'post arr to database
    ws.Cells(lRow, 1).Resize(, UBound(arr)).Value = arr
'inform user
    MsgBox "Record Added", 64, "Record Added"
'clear controls
    For i = 1 To UBound(arr)
        Me.Controls(ControlsArr(i)).Text = ""
    Next i
            
UserForm2.Hide
UserForm1.Show
End Sub

Note Option Base 1 statement which MUST go at VERY TOP of your forms code page OUTSIDE any procedure.

As name of the commandButton in your code is not shown I have used name "CommandButton1" but you should change as required.

Dave