Link to home
Start Free TrialLog in
Avatar of Bimmy Thomas
Bimmy Thomas

asked on

Manually enter date in datepicker

Hello All,

Excel beginner here.

I have created a datepicker in excel.

What I'm looking for is, to add a helper box like textbox or label on the userform ,wherein, the user manually enters date which then gets selected in the calendar.

If this is possible can you provide me with a code.

I have searched the net for possible answers but couldn't find anything relevant.

Your assistance will be appreciated.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

You cannot enter to a Label manually. Using a TextBox seems to be surplus to requirements.

This example does what I think you are asking, but why do it this way?

You should also be aware that DatePickers are not always available on every computer and so can cause problems. Read this
DatePicker-example.xlsm
I forgot to say that the date should be entered in the TextBox and when the user exits the TextBox the date is changed in the DatePicker
Avatar of Bimmy Thomas
Bimmy Thomas

ASKER

Hi Roy,

Thanks for responding.

Kindly bear with me as I'm new to vba.

I downloaded the sheet.

Went into the macro window by doing Alt + F11 and and ran the macro by pressing F5. A userform poped-up with a textbox

I did the following but nothing happens -

Created a calender on the userform and named it as DTPicker1 and entered date in the textbox and nothing happened. Tried entering date in formats 14Jan or 14Jan17 or 14Jan207.

Have attached sheet for reference.
Sorry... was finding it difficult to attach file

DatePicker-example1.xlsm
The example that I posted contains a UserForm with a DatePicker and a TextBox  with code to allow the user to enter a date into the TextBox then when the user tabs from the TextBox that date is entered to the DatePicker. This is totally unnecessary thought.

The code works with your example, but explain why you need the TextBox
I added Shape to show DatePicker to Roy's Sheet,

Question what's the purpose of having such UserForm? Do you want these dates to be anywhere in your sheet?
DatePicker-example-2.xlsm
@Roy : Getting error as below and .DTPicker1 is getting highlighted -

Method or data member not found

@Shums : Getting error as below -

Could not load an object because it is not available on this machine

Is there anything that I'm doing wrong...???
are you using windows or mac version? If it is Windows, then which version, 32 bit or 64 bit.
Using Windows 7 Ultimate, 64 bit operating System and  Microsoft Office 2007.

Is there any file missing that needs to be installed.
Bimmy, try following this repair process and see, if it helps.

Try the below steps and check if it helps fix the issue;
Click on start->control panel->programs and features->right click on the Office 2007 program->click on change->add or remove features-> click on the drop down arrow which is before the Microsoft Office->click on Run all from my computer->Continue.
Once completed restart the computer and then Open Excel to check if the issue occurs.
 
Also check if there are any files in the startup folder .If you find any files take a back and delete them. Go to the below location to delete the files: C:\Program Files\Microsoft Office\Office14\XLSTART
C:\Users\<User Name>\AppData\Roaming\Microsoft\Excel\XLSTART
@ Shums : Will get back to you on this
Both my example and the one that you uploaded work fine on my PC. Watch the calendar when you exit the TextBox the date changes to match the TextBox. I have added a second TextBox that will confirm the date. Enter the date into TextBox1 then tab out run the code. See attached example

Do not delete files in XLStart unless you know what you are doing, sometimes Excel addins are in there. You should not need to make any changes to Excel at all
DatePicker-example1.xlsm
@ Shums : I was finally able to find out that on my excel there is no datepicker. It's actually a calendar.

I was able to find this out by clicking on calendar and in the properties window, click on the blank box next to (About). A pop-up gives the detail as -

Microsoft Office Access Calendar Version
Copyright 1987-2006 Microsoft Corporation


So, I changed the code as below and on the userform inserted Calendar and it WORKS PERFECTLY -

[quote]Option Explicit

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox1.Value) Then Me.Calendar1.Value = Me.TextBox1.Value
End Sub[/quote]

Open in new window


One final enhancement to the above code will be highly appreciated.

Would also like to Add or Subtract dates.

So, in textbox if I enter 05 Jan 17 +1 (No spaces between + and 1) or 05 Jan 17 -1 (No spaces between - and 1), macro should take me to 07 Jan17 or 06 Jan17 respectively. Numbers Added or Subtracted will increase or decrease. Few examples as below -

05 Jan 17 -100 (Macro should deduct 100 days from 05 Jan17)
05 Jan 17 +100 (Macro should add 100 days to 05 Jan17)
You cannot add a day to date like that in one TextBox. Why are you using a TextBox? It seems totally unnecessary to me an re-inventing the wheel. A DatePicker or Calendar control is intended to replace entering dates into a TextBox

By the way the name of the control in your example is DTPicker1, I assumed that you had changed it to that

User generated image
Here's an example that does what you want using SpinButton control
DatePicker-example1.xlsm
@ Roy : After my discovery (stated above), I changed the code as below and it also works PERFECTLY -

Option Explicit



Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.TextBox1.Value) Then
        Me.Calendar1.Value = Me.TextBox1.Value
        Me.TextBox2.Text = "Check the calendar now"
        MsgBox "Calendar date should be " & Format(Me.TextBox1.Value, "long date"), vbInformation, "Date changed"
    End If
End Sub

Private Sub UserForm_Click()

End Sub

Open in new window


I know what I'm asking for is something weird, but that's exactly what I want. The idea here is to allow manual entry of dates. (If textbox or something similar can be used, then do advise)

Your assistance with regards to addition and subtraction of dates will be appreciated.  

One textbox to enter date and second textbox to enter number of dates to be added or subtracted.

So, if I enter date as 05 Jan 17 in textbox1 and +1 or -1 in textbox2 then calendar should reflect the result.

Have attached working code. Date.xlsb
Looks like your answer crossed with my previous post, see the attached example that allows the date to be changed with a SpinButton control.
@ Roy : My line of work requires addition and subtraction of days. Keeps increasing or decreasing.

Addition or subtraction involves days ranging from 05 or 11 or 53 or 210 days. SpinButton will not be helpful here.

I humbly request you to assist
@ Roy : I would request you to  provide the code on the excel file I have attached.

Date.xlsb
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
let me test and get back
Roy excellent work

Bimmy, that's the matching best code.
Best thing happened to me after joining the forum.

Roy had the patience to listen, understand and come out with the solution.

Can't thank enough for the assistance provided.

The calendar will increase our productivity factor many folds.

Roy.... U ROCK
Pleased to help