PMRetired2012
Board Regular
- Joined
- Aug 6, 2019
- Messages
- 123
What want to do is after i transfer data from a userform to a worksheet i want the date picker to be blank when im ready to enter the next set of data into the userform. i am going attach my code that is have for the userform. im not sure what is going on when i transfer data and open the userform again it has the date picker alrady populated and i want it to be blank so i can choose the date. Feel free to change my code to where it will work just hightlight the changes so i can see what has been done.
Private Sub CommandButton1_Click()
Dim lr As Long
Application.ScreenUpdating = False
Sheet = ComboBox1.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
'Update each class sheet Code
Sheets(Sheet).Select
Set findBlank = Range("A:A").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker1
ActiveCell.Offset(0, 1).Value = TextBox1.Text
ActiveCell.Offset(0, 2).Value = ComboBox2.Text
ActiveCell.Offset(0, 3).Value = ComboBox3.Text
ActiveCell.Offset(0, 4).Value = TextBox2.Text
ActiveCell.Offset(0, 5).Value = TextBox3.Text
ActiveCell.Offset(0, 6).Value = DTPicker2
ActiveCell.Offset(0, 7).Value = TextBox4.Text
ActiveCell.Offset(0, 8).Value = TextBox5.Text
ActiveCell.Offset(0, 9).Value = TextBox6.Text
ActiveCell.Offset(0, 15).Value = TextBox7.Text
ActiveCell.Offset(0, 16).Value = TextBox8.Text
'Clear Form
ComboBox1.Text = ""
DTPicker1.Value = ""
TextBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
DTPicker2.Value = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
End Sub
Private Sub UserForm_Initialize()
'Combobox1
With ComboBox1
.AddItem "JANUARY"
.AddItem "fEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With
'ComboBox2
'ComboBox3
With ComboBox3
.AddItem "UTILITY"
.AddItem "RESALE"
.AddItem "INSURANCE"
End With
End Sub
Private Sub CommandButton1_Click()
Dim lr As Long
Application.ScreenUpdating = False
Sheet = ComboBox1.Text
If Sheet = "" Then
MsgBox "Select Month", vbInformation, "Error"
Exit Sub
End If
'Update each class sheet Code
Sheets(Sheet).Select
Set findBlank = Range("A:A").Find(What:="", lookat:=xlWhole)
findBlank.Select
ActiveCell.Value = DTPicker1
ActiveCell.Offset(0, 1).Value = TextBox1.Text
ActiveCell.Offset(0, 2).Value = ComboBox2.Text
ActiveCell.Offset(0, 3).Value = ComboBox3.Text
ActiveCell.Offset(0, 4).Value = TextBox2.Text
ActiveCell.Offset(0, 5).Value = TextBox3.Text
ActiveCell.Offset(0, 6).Value = DTPicker2
ActiveCell.Offset(0, 7).Value = TextBox4.Text
ActiveCell.Offset(0, 8).Value = TextBox5.Text
ActiveCell.Offset(0, 9).Value = TextBox6.Text
ActiveCell.Offset(0, 15).Value = TextBox7.Text
ActiveCell.Offset(0, 16).Value = TextBox8.Text
'Clear Form
ComboBox1.Text = ""
DTPicker1.Value = ""
TextBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
DTPicker2.Value = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
End Sub
Private Sub UserForm_Initialize()
'Combobox1
With ComboBox1
.AddItem "JANUARY"
.AddItem "fEBUARY"
.AddItem "MARCH"
.AddItem "APRIL"
.AddItem "MAY"
.AddItem "JUNE"
.AddItem "JULY"
.AddItem "AUGUST"
.AddItem "SEPTEMBER"
.AddItem "OCTOBER"
.AddItem "NOVEMBER"
.AddItem "DECEMBER"
End With
'ComboBox2
'ComboBox3
With ComboBox3
.AddItem "UTILITY"
.AddItem "RESALE"
.AddItem "INSURANCE"
End With
End Sub