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.
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.
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
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.
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.
ASKER
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
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
Question what's the purpose of having such UserForm? Do you want these dates to be anywhere in your sheet?
DatePicker-example-2.xlsm
ASKER
@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...???
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.
ASKER
Using Windows 7 Ultimate, 64 bit operating System and Microsoft Office 2007.
Is there any file missing that needs to be installed.
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\Micr osoft\Exce l\XLSTART
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\Micr
ASKER
@ 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
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
ASKER
@ 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 -
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)
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]
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
By the way the name of the control in your example is DTPicker1, I assumed that you had changed it to that
Here's an example that does what you want using SpinButton control
DatePicker-example1.xlsm
DatePicker-example1.xlsm
ASKER
@ Roy : After my discovery (stated above), I changed the code as below and it also works PERFECTLY -
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
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
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.
ASKER
@ 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
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
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me test and get back
Roy excellent work
Bimmy, that's the matching best code.
Bimmy, that's the matching best code.
ASKER
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
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
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