• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check date on UserForm

K

kaushik03

Member
Hello friends,


I have a "Data Entry" userform which users use to input ceratin data which are getting captured in a particula sheet in the same workbook.

In the userform, there is a textbox which holds the date ("mm-dd-yyyy" format) as user selects the date from a calender(placed in this "Data Entry" userform).


I want the code to prevent users from entering any data on the form and give a message (and should close the form) if the date(on which they open the form to submit the info) > 23rd of that month.


To be more precise, IF user's input date(in other words, the date when they open the form)>23rd of that month THEN alert message should be popped up and form should be closed ELSE proceed.


[Note:So, date(23rd) should be fixed but "month" and year should change as the time progress....I am not able to make this dynamic]

I tried doing this by using 'datedif' but not able to get the right solution.


Please note that I am not able to share the excel as I am posting this from my office network.


However, hope, I am able to explain my requirement correctly.


Please let me know in case of any doubts.


Any help is much appreciated.


Regards,

Kaushik
 
Something that looks like this perhaps?

[pre]
Code: 
If Day(DateChosen) > 23 Then
'Do something
Else
'Do something else
End If
[/pre]
 
Hi, kaushik03!


First of all and regarding function DATEDIF, please give a look to this link:

http://chandoo.org/forums/topic/excel-2007


Now to your question.


Put this code into the VBA section of the user form, adjusting form name and replacing Click event code for whatever you might need.


-----

[pre]
Code: 
Option Explicit

Private Sub UserForm_Click()
MsgBox "yes"
End Sub

Private Sub UserForm_Activate()
If Day(Now()) > 23 Then UserForm_Terminate
End Sub

Private Sub UserForm_Initialize()
On Error Resume Next
If Day(Now()) > 23 Then
Load UserForm1
UserForm1.Hide
MsgBox "Operation not enabled after 23rd. day of the month." & vbCrLf & _
"Process is cancelled!", vbSystemModal + vbCritical + vbOKOnly, "Warning..."
End If
End Sub

Private Sub UserForm_Terminate()
Unload Me
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
@Luke:


It works perfectly fine..yes, I should do this through 'Day' not 'datedif'. Thank you for guiding a 'newbie' in VBA..


@SirJB7: Thank you for sharing the datedif article and code in detail.It's really great


You guys are really awesome.


I have been learning a lot from you people everyday in this forum.


Best regards,

Kaushik
 
Hi, kaushik!


All the structure of procedures (_Activate, _Initialize, _Terminate) is to prevent the form even to be displayed. If that's not necessary, just keep _Activate and replace the THEN action by an Unload Me.


Regards!


PS: so do we... particularly myself learning even bengali, thanks to you and your friend Debraj Roy :p
 
SirJB7,


I did exactly the same by replacing the THEN action by an Unload Userform1 to suit. THank you for this instruction again.


And, Bengali, indeed, a very sweet language as Spanish is...


Regarrds,

Kaushik
 
Hi Koushik,


Code: 
In the userform, there is a textbox which holds the date ("mm-dd-yyyy" format) as user selects the date from a calender(placed in this "Data Entry" userform).


Can you please share me the code for 'DateTimePicker' Toolbox in VBA.


PS:

* I search but I don't have the below DLL. C:windowssystem32mscomct2.ocx

* I dont have VB6 currently installed on my system.


@SirJB7

Is there any option to set Application.InputBox("Please Enter Date Only" Type:=64), so that I can restrict only for DATE (via array) or something like this..


PS: Please dont tell me to look at the URL, as it was not digestible for me..

http://chandoo.org/forums/topic/how-to-add-datepicker-in-excel-2010-vba-form
 
Hi, Debraj Roy!


What Excel version do you use?

I've been using InputBox since I can remember (not in Visicalc, that was my dad) and I've never had such issue, it has been always available.

In my installation, the referenced file for "Microsoft Windows Common Controls 6.0 (SP6)" is the file C:WindowsSYSWOW64MSCOMCTL.OCX (I use Excel 2010 32 bits over Win 7 Ultimate 64 bits).


The code used for check and download the DateTimePicker is highly indigestible but not necessary I think. Tried with this part, extracted from other comment of same link?


-----

[pre]
Code: 
Other than having to give a user these type of instructions (assuming they're correct):

How to install 'Microsoft Date & Time Picker' if it's missing:

* Download MSCOMCT2.OCX   http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB

* Save downloaded file in relevant directory:

For Windows 95, 98, or ME:
c:windowssystem

For Windows NT or 2000:
c:WINNTsystem32

For Windows XP or 7:
c:windowssystem32

* Register the library file:
eg:  Start > Run > regsvr32 windowssystem32MSCOMCT2.OCX

For Windows 95, 98, or ME:
regsvr32 windowssystemMSCOMCT2.OCX

For Windows NT or 2000:
regsvr32 WINNTsystem32MSCOMCT2.OCX

For Windows XP or 7:
regsvr32 windowssystem32MSCOMCT2.OCX

You should see a message saying "DllRegisterServer ... succeeded"
[/pre]
-----


Regards!
 
Hi Debraj,


I am not sure what you are asking but if you are asking for code that I have written for picking up the date from calender and hold it on textbox, then see the file here.


https://hotfile.com/dl/164353512/7f1c36d/DatePicker_code.xlsm.html


Userform3 is the one where I have placed the calender and written the code


Regards,

Kaushik
 
@ SirJB7,

Thank You BOSS..

I hope ,I am missing my VB6, and need to install.. :)

all DLL are already available on the Installation Disk. :)


regarding InputBox, they never make any issue.

My issue is with Application.InputBox, which has a TYPE parameter, to restrict Input without any extra Code.

0-A formula

1-A number

2-Text (a string)

4-A logical value (True or False)

8-A cell reference, as a Range object

16-An error value, such as #N/A

64-An array of values


I am hoping to use the '64' Application.InputBox("Please Enter Date Only" Type:=64) so that IsDate() funtion no need to Apply.. Or please show me an example to use 64-An array of values in Application.InputBox


@Koushik,

Thank you for the code. You have used.. Microsoft Calendar Control 2007..

but Missing in my system :(
 
Hi Debraj ,


A good idea is available here :


http://www.mrexcel.com/forum/showthread.php?538809-VBA-Application-InputBox-Type-Problems


Using Type:=0 allows the calling procedure to handle input data ; if you enter a date , the method returns a string which is actually a date number with an "=" sign prefixing it !


For example , if you enter 07/25/2012 , it returns a string value "=41115" ; using this is just a matter of stripping the "=" sign , and using CDate.


Narayan
 
Hi, Debraj Roy!

Would you believe me if I tell you that I've never heard before today about Application.InputBox?

Not having heard, not used, still don't get the usefulness, so have to avoid helping you, unless you upload a sample file from where I could see how you intend to use it.

Regards!
 
@ Narayan...

Thanks buddy.. for making my Idea live..:)

Yes.. I hope Now i can adapt this..


@ JB-007

No, I will not believe and assume that you are encouraging me as usual.. and want more respect from me.. :)


@ both..

Why "Excel Ninja" is Not with Hyperlink for both of YOU.. now every member's wants to know more about you.. Please..
 
@Debraj Roy


Hi!


About my personally addressed comment:


You're free to believe what you want... but this seems to be a Ripley case (Believe it or not!, remember?).

You shouldn't think that at least this Excel Ninja knows everything about Excel yet, but rather still learns something new frequently...

As Mr. Albert once said, "We are all very ignorant, but not all ignorant of the same things".


About shared comment:


Clicking on a user name automatically links you to that user profile at chandoo.org

If that user entered a valid link in its profile field WebSite, user badge or type automatically gets hiperlinked to that URL. That's the way it works. Other than a valid URL you can post an email address too.


Personally I'm very lazy and more shy indeed, so that combination seems to result in the absence of such a presentation page, either within chandoo's website or at another location. Idea which in fact flashes the alert lights of privacy and paranoia,... remember Conspiracy Theory with Mel Gibson and Julia Roberts, or Enemy Of The State with Gene Hackman and Will Smith? Sometimes I do :p


Regards!
 
Ditto to what SirJB7 says about privacy. Maybe someday we'll write up an article about ourselves like Hui has done, but until then, we will be like Batman. We could be anybody. =)
 
@Debraj Roy

Hi!

You might have noticed that I wrote privacy and paranoia, and Luke M only privacy, so it'd be reasonable if you expect his article arriving first :p

Regards!

PS: But thinking it again... at least I don't hide my avatar's face as he does...
 
@Dhamo


Hi


You can use the code as in Calendar UserForm as


Private Sub UserForm_Initialize()

Calendar1.Value = Date

End Sub


if you are checking the kaushik file and you want to update date field then add this line in the UserForm3 or 4


Calendar1.Value = Date


Hope it will solve your problem


Thanks


SP
 
Thanks a lot SP.


I will be sending this xlsm file to my team and they didn't configured the 'calendar control'. Getting error.


Do you have any idea on how to resolve this?
 
Error: "Compile error in hidden module : MRibbenEditor" and I couldn't see any calendar in userform3.
 
@Dhamo


Hi


Sorry i have no idea about the error but i think it is problem in your excel version, which version you are using any how please follow the link. I think with this link you can solve your problem


http://answers.microsoft.com/en-us/office/forum/officeversion_other-excel/compile-error-in-hidden-module-this-workbook/5da89258-34fa-4bcc-96bc-223787da6ada


and you told me you didn't found any calendar in UserForm3 did you download the correct file which is Kaushik was uploaded if not then please find the link again


http://chandoo.org/forums/topic/check-date-on-userform#post-33849


I have another Doubt when you select the UserForm3 is it display with blank ?!


Thanks


SP
 
SP,


I am using Office 2k7, I am able to see the user form with calendar in home PC which Kaushik was uploaded. But I couldn't see the calendar control in user form 3 from office PC. It is showing 'Select Date' button alone.


Even I can add the calendar control to my office PC, but I am not sure it will work if I send the file to my colleagues as they perhaps enabled calendar control.
 
You must log in or register to reply here.
Back
Top