VBA user form that returns data to next empty row in excel spreadsheet and limit data type for date in specific textbox

0
votes

I am trying to make an excel user form through VBA. It should:

  1. Take the data entered in the textboxes and put it into the next empty row in a specific sheet
  2. In some textBoxes (specifically TextBox3, TextBox4, TextBox5 and TextBox6) should only be entered date data type. If the user enters any other data format an error message should appear and the form should close, not filling the next empty row that it was about to fill in the sheet.
  3. All textBoxes should have an input, except TextBox5 and TextBox6, these could be empty, if any other textbox is empty an error message should appear.
  4. After the proper inputs are made a confirmation msgbox should appear for the user to check any error, before the form closes

Step 1 I've menaged to do but 2 and 3 are not working properly with what I have so far (I can me more specific with the errors if needed). I'm new to VBA and programming and think I've messed with the 'Ifs'. Thanks in advence for any help!

Private Sub CommandButton1_Click()
'Check if data in TextBox is date
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not ((IsDate(TextBox3.Text)) And (IsDate(TextBox4.Text)) And (IsDate(TextBox5.Text)) And (IsDate(TextBox6.Text))) Then
        MsgBox "Date Required"
        Cancel = True
    Else

'Last ditch validation before committing input values to document.
    Dim booConfirmation As Boolean
    'Check for data
    If Len(TextBox1.Text) = 0 Or Len(TextBox2.Text) = 0 Or Len(TextBox3.Text) = 0 Or Len(TextBox4.Text) = 0 Or Len(TextBox7.Text) = 0 Or Len(TextBox8.Text) = 0 Or Len(TextBox9.Text) = 0 Or Len(TextBox10.Text) = 0 Or Len(TextBox11.Text) = 0 Then
        MsgBox "Empty entries", vbOKOnly, "Input Error"    
    End If

    'Display name so user can check and confirm.
    booConfirmation = MsgBox("Are the entries " & TextBox1 & " " & TextBox2 & " " & TextBox3 & " " & TextBox4 & " " & TextBox7 & " " & TextBox8 & " " & TextBox9 & " " & TextBox10 & " " & TextBox11 & "correct?", vbYesNo)

    'If booConfirmation Then
    If booConfirmation = vbNo Then

MsgBox "Please correct the entries"

Set ws = Sheets("Inputs")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1

ws.Range("B" & LastRow).Value = "" 'if entries are incorrect erase the data that should be entered into the sheet'
ws.Range("C" & LastRow).Value = ""
ws.Range("D" & LastRow).Value = ""
ws.Range("E" & LastRow).Value = ""
ws.Range("F" & LastRow).Value = ""
ws.Range("G" & LastRow).Value = ""
ws.Range("H" & LastRow).Value = ""
ws.Range("I" & LastRow).Value = ""
ws.Range("J" & LastRow).Value = ""
ws.Range("K" & LastRow).Value = ""
ws.Range("L" & LastRow).Value = ""

Exit Sub



 Else
Set ws = Sheets("Inputs")
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
    ws.Range("B" & LastRow).Value = TextBox1.Text 'Adds the TextBox1 into Col B & Last Blank Row
    ws.Range("C" & LastRow).Value = TextBox2.Text 'Adds the TextBox2 into Col C & Last Blank Row
    ws.Range("D" & LastRow).Value = TextBox3.Text
    ws.Range("E" & LastRow).Value = TextBox4.Text
    ws.Range("F" & LastRow).Value = TextBox5.Text
    ws.Range("G" & LastRow).Value = TextBox6.Text
    ws.Range("H" & LastRow).Value = TextBox7.Text
    ws.Range("I" & LastRow).Value = TextBox8.Text
    ws.Range("J" & LastRow).Value = TextBox9.Text
    ws.Range("K" & LastRow).Value = TextBox10.Text
    ws.Range("L" & LastRow).Value = TextBox11.Text



End If
End If

Unload Me

End Sub
1
excelvba
That's lots of requirements for 1 question. I would suggest you do this 1 at a time. Post a question with your first requirement that isn't working. Once resolved, post the next requirement - Zac
Alright I'll edit the post, thanks! - Mariana Ferreira
Before you go too far change your exit procedure to check only the field you are exiting. As you have it coded I could not leave TextBox3 if there is something wrong with TextBox4 (so how could I ever fix TextBox4). You should also work hard on getting indentation of code right, makes checking your IF's much easier - Tin Bum
Also use an OK button or similar to run the overall validation of the form. So when the user clicks that button all fields are checked - you're mixing up 2 separate capabilities into a single Exit procedure that should only check that one field - Tin Bum

1 Answers

0
votes

This is not an answer this is just to help you see it more clearly All I've done is re-arrange your code and provide consistent indentation

You had, no end sub for one procedure and code mixed up between the two, with an endif that belonged in one in the other

Anyway - this is your code ... with only minor changes .... make exit procedure just check the 1 field

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  'Check if data in TextBox is date
  If Not (IsDate(TextBox3.Text)) Then ' And (IsDate(TextBox4.Text)) And (IsDate(TextBox5.Text)) And (IsDate(TextBox6.Text))) Then
    MsgBox "Date Required"
    Cancel = True
  Else
  End If
End Sub

Private Sub CommandButton1_Click()
  'Last ditch validation before committing input values to document.
  Dim booConfirmation As Boolean
  'Check for data

  'Display name so user can check and confirm.
  booConfirmation = MsgBox("Are the entries " & TextBox1 & " " & TextBox2 & " " & TextBox3 & " " & TextBox4 & " " & TextBox7 & " " & TextBox8 & " " & TextBox9 & " " & TextBox10 & " " & TextBox11 & "correct?", vbYesNo)

  'If booConfirmation Then
  If booConfirmation = vbNo Then
    MsgBox "Please correct the entries"

    Set ws = Sheets("Inputs")
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1

    ws.Range("B" & LastRow).Value = "" 'if entries are incorrect erase the data that should be entered into the sheet'
    ws.Range("C" & LastRow).Value = ""
    ws.Range("D" & LastRow).Value = ""
    ws.Range("E" & LastRow).Value = ""
    ws.Range("F" & LastRow).Value = ""
    ws.Range("G" & LastRow).Value = ""
    ws.Range("H" & LastRow).Value = ""
    ws.Range("I" & LastRow).Value = ""
    ws.Range("J" & LastRow).Value = ""
    ws.Range("K" & LastRow).Value = ""
    ws.Range("L" & LastRow).Value = ""

    Exit Sub
 Else
    Set ws = Sheets("Inputs")
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
    ws.Range("B" & LastRow).Value = TextBox1.Text 'Adds the TextBox1 into Col B & Last Blank Row
    ws.Range("C" & LastRow).Value = TextBox2.Text 'Adds the TextBox2 into Col C & Last Blank Row
    ws.Range("D" & LastRow).Value = TextBox3.Text
    ws.Range("E" & LastRow).Value = TextBox4.Text
    ws.Range("F" & LastRow).Value = TextBox5.Text
    ws.Range("G" & LastRow).Value = TextBox6.Text
    ws.Range("H" & LastRow).Value = TextBox7.Text
    ws.Range("I" & LastRow).Value = TextBox8.Text
    ws.Range("J" & LastRow).Value = TextBox9.Text
    ws.Range("K" & LastRow).Value = TextBox10.Text
    ws.Range("L" & LastRow).Value = TextBox11.Text
End If

Unload Me

End Sub