Clear userform fields upon submit

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,752
Office Version
  1. 2007
Platform
  1. Windows
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.


Code: 
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Code: 
.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
 
Upvote 0
To clear all the Coboboxes and textboxes in your UserForm.
Try this:

Code: 
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
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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>​
 
Last edited:
Upvote 0
Sorry i supplied the wrong code.

The correct code that works for me is shown below.

Code: 
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
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Two values on userform but only send one value to worksheet
Replies
5
Views
248
ipbr21054
I
I
  • Question Question
Delete row on another sheet from a userform
2 3
Replies
22
Views
744
ipbr21054
I
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
362
ipbr21054
I
I
  • Solved
DTPicker1 value to worksheet advice
Replies
2
Views
172
ipbr21054
I
I
  • Solved
Correct sytax for yes / no Msgbox
2
Replies
10
Views
413
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,223,956
Messages
6,175,616
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top