Clearing datepicker when i upload data from a userform

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
If you are using the native VBA (MonthView) DTPicker - it accepts a default value when initialized, although this can be changed to any date upon submission of data.

Could you not revert back to, say, today's date when you click on your commandbutton?

VBA Code:
Me.DTPicker1.Value = Date
 

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110
Where would I put the code: me.DTPicker.value = Date? I need to know after what part in the code I sent you in the thread.
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Replace:
VBA Code:
DTPicker1.Value = ""
With
VBA Code:
Me.DTPicker1.Value = Date

I see you have DTPicker2 there as well. You can treat it similarly.
 

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110

ADVERTISEMENT

Replace:
VBA Code:
DTPicker1.Value = ""
With
VBA Code:
Me.DTPicker1.Value = Date

I see you have DTPicker2 there as well. You can treat it similarly.
I guess what im really asking when i open up userform i want the Datepickers to be blank and not have a default date in the ComboBox. How do you code it that way or do i change something in the properties of the combobox?

Thanks
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Are you using a calendar version of DTPicker - I'm trying to assess what you are physically using?
 

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110

ADVERTISEMENT

Are you using a calendar version of DTPicker - I'm trying to assess what you are physically using?
Im using microsoft calendar version of DTPicker. When i click on Date picker in my controls toolbox a full calendar comes up and i can pick my date i want to use. i had to download the date picker to get it into my control toolbox before i could use it.
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Is your chosen date output then linked to a combobox? I don't think the Calendar control itself can be made to read nothing.
 

Mattlake

New Member
Joined
Apr 9, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
HI

This is my first time offering help so if it doesn't make sense please say

I was having the same issue, the date output on my DTpicker is is in a calendar control.

I found putting the below in the script with the other fields that I needed clearing worked for me.

Me.Text_datefrom.Value = "14/4/20"
Me.Text_dateto.Value = "14/4/20"

It may help

Regards

Matthew
 

Watch MrExcel Video

Forum statistics

Threads
1,130,124
Messages
5,640,250
Members
417,131
Latest member
Seanr19871

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
Top