Clear userform fields upon submit

[ipbr21054]

Morning,

After i submit my form i would like to the have the field contents cleared so i can then start my second data input etc.

My code in use is supplied below.

Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
    
    .Range("A6").Value = Me.TextBox1.Text
    .Range("B6").Value = Me.ComboBox1.Text
    .Range("C6").Value = Me.ComboBox2.Text
    .Range("D6").Value = Me.ComboBox3.Text
    .Range("E6").Value = Me.ComboBox4.Text
    .Range("F6").Value = Me.ComboBox5.Text
    .Range("G6").Value = Me.ComboBox6.Text
    .Range("H6").Value = Me.ComboBox7.Text
    .Range("I6").Value = Me.ComboBox8.Text
    .Range("J6").Value = Me.ComboBox9.Text
    .Range("K6").Value = Me.ComboBox10.Text
    .Range("L6").Value = Me.ComboBox11.Text
    .Range("M6").Value = Me.TextBox2.Text
    .Range("N6").Value = Me.ComboBox12.Text
    .Range("O6").Value = Me.TextBox3.Text
    .Range("P6").Value = Me.TextBox4.Text
    .Range("Q6").Value = Me.TextBox5.Text
End With
    .Me.TextBox.Text = ""
    .Me.ComboBox1.Text = ""
    .Me.ComboBox2.Text = ""
    .Me.ComboBox3.Text = ""
    .Me.ComboBox4.Text = ""
    .Me.ComboBox5.Text = ""
    .Me.ComboBox6.Text = ""
    .Me.ComboBox7.Text = ""
    .Me.ComboBox8.Text = ""
    .Me.ComboBox9.Text = ""
    .Me.ComboBox10.Text = ""
    .Me.ComboBox11.Text = ""
    .Me.TextBox2.Text = ""
    .Me.ComboBox12.Text = ""
    .Me.TextBox3.Text = ""
    .Me.TextBox4.Text = ""
    .Me.TextBox5.Text = ""
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus


End Sub

thanks

 

[Ramballah]

.Me.Textbox.ClearContents
.Me.ComboBox1.ClearContents

Etc should do the job.
This just only clears the stuff that is inside the cell. so the layout and stuff will stay. and conditional formatting

 

[My Aswer Is This]

To clear all the Coboboxes and textboxes in your UserForm.
Try this:

Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.ComboBox
            ctrl.Value = ""
    End Select
    
Next ctrl
End Sub

 

[ipbr21054]

@MAITH that worked a treat.

Thanks

 

[My Aswer Is This]

@MAITH that worked a treat.

Thanks

Glad I was able to help you.
I see your still working on the same project as last week or maybe 2 weeks ago. Or maybe your working on a new project.
Wishing you the best.

 

[ipbr21054]

This is another form.
I took your form code from that sheet & used it on another sheet.
The difference being the other sheet placed it in the first available last row where this new one inserted it into Row 6 like shown below.

It works for me but doesnt mean its written correctly in respect of its code being compressed etc.

Private Sub CommandButton1_Click()    Dim ws As Worksheet


    Set ws = Sheets("DATABASE")




    ws.Range("1").Value = TextBox1.Text
    ws.Range("2").Value = ComboBox1.Text
    ws.Range("3").Value = ComboBox2.Text
    ws.Range("4").Value = ComboBox3.Text
    ws.Range("5").Value = ComboBox4.Text
    ws.Range("6").Value = ComboBox5.Text
    ws.Range("7").Value = ComboBox6.Text
    ws.Range("8").Value = ComboBox7.Text
    ws.Range("9").Value = ComboBox8.Text
    ws.Range("10").Value = ComboBox9.Text
    ws.Range("11").Value = ComboBox10.Text
    ws.Range("12").Value = ComboBox11.Text
    ws.Range("13").Value = TextBox2.Text
    ws.Range("14").Value = ComboBox12.Text
    ws.Range("15").Value = TextBox3.Text
    ws.Range("16").Value = TextBox4.Text
    ws.Range("17").Value = TextBox5.Text
    
TextBox2.SetFocus
TextBox2.Value = Now
TextBox1 = Format(TextBox2.Value, "dd/mm/yyyy")




Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6").Select
Range("A6:Q6").Borders.LineStyle = xlContinuous
Range("A6:Q6").Borders.Weight = xlThin
Range("A6:Q6").Interior.ColorIndex = 6
Range("M6") = Date
Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
Range("$Q$6").HorizontalAlignment = xlCenter
Unload DatabaseInput
End Sub


Private Sub CommandButton2_Click()
Unload DatabaseInput
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox3_Change()
    TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub

 

[My Aswer Is This]

This line of code cannot work:

ws.Range("1").Value = TextBox1.Text

It would need to be something like this:

ws.Range("A1").Value = TextBox1.Text
​

_{^{<strike>
</strike>}}​

 

[ipbr21054]

Sorry i supplied the wrong code.

The correct code that works for me is shown below.

Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
     Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("A6").Select
     Range("A6:Q6").Borders.LineStyle = xlContinuous
     Range("A6:Q6").Borders.Weight = xlThin
     Range("A6:Q6").Interior.ColorIndex = 6
     Range("M6") = Date
     Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
     Range("$Q$6").HorizontalAlignment = xlCenter
     
    .Range("A6").Value = Me.TextBox1.Text
    .Range("B6").Value = Me.ComboBox1.Text
    .Range("C6").Value = Me.ComboBox2.Text
    .Range("D6").Value = Me.ComboBox3.Text
    .Range("E6").Value = Me.ComboBox4.Text
    .Range("F6").Value = Me.ComboBox5.Text
    .Range("G6").Value = Me.ComboBox6.Text
    .Range("H6").Value = Me.ComboBox7.Text
    .Range("I6").Value = Me.ComboBox8.Text
    .Range("J6").Value = Me.ComboBox9.Text
    .Range("K6").Value = Me.ComboBox10.Text
    .Range("L6").Value = Me.ComboBox11.Text
    .Range("M6").Value = Me.TextBox2.Text
    .Range("N6").Value = Me.ComboBox12.Text
    .Range("O6").Value = Me.TextBox3.Text
    .Range("P6").Value = Me.TextBox4.Text
    .Range("Q6").Value = Me.TextBox5.Text
End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus


End Sub

 

[My Aswer Is This]

Your code looks Ok.
There could be a change or two but nothing real important.

If I remember correctly.

Your first objective was to send data from the Userform to the sheet.
The second objective was to take data from the sheet and put it in the Userform.
The third objective was to take modified data from the Userform and put it back into the sheet.

Have you been able to achieve all these goals?

 

[ipbr21054]

You are talking about this form here or another ?