TextBox values as filter criteria - dates
Hello everyone,
I have a difficulty to use TextBox Values as criteria for an automated filter... Could anyone of you help me? See the attached file for reference (test.xlsm). I would like users to input dates (from - till) in TextBoxes on UserForm and filter data accordingly. This code works fine with exact text values, but not with dates and "=<":
I would be grateful for your help. I did not manage that with Google.
Please Login or Register to view this content.
Thank you. Michal
The code is working fine for me...only thing you need to ensure is to enter the date in mm/dd/yy format
Vivek.jain, thank you for your quick reply. The thing is that nobody in our country can easily understand the mm/dd/yyyy format, because the national standard is dd.mm.yyyy and it would be clumsy to force users to fill in different date-format to UserForm while seeing standard date-formats (dd.mm.yyyy) in the sheets.
In our localized Excel versions, we never use mm/dd/yyyy. Any idea how to get around this?
Last edited by MKrop; 09-20-2013 at 04:59 AM. Reason: mistake in format
test.xlsmuse the below code on the click event of the ok button.
Please Login or Register to view this content.
Last edited by vivek.jain; 09-20-2013 at 05:42 AM.
I appreciate your time you put into this, but I am getting an error in this part of the code now:
Even in your file you uploaded. Any idea?Please Login or Register to view this content.
which version of excel are you using?
try usingPlease Login or Register to view this content.
try usingPlease Login or Register to view this content.
My Excel version in 2007 now. The problem is probably in A2 and B2, in these 2 cells the #VALUE! error occurs:
Capture.PNG
When I run your code in VBA, the UserForm pops-up and after filling some dates (e.g. from 1.1.2013 till 30.5.2013), the macro stops at those 2 lines I mentioned earlier and #VALUE! error in A2 and B2 occurs.
(post deleted for duplication)
Last edited by MKrop; 09-20-2013 at 08:01 AM. Reason: duplication
I have written the macro using Excel 2007 and it is working perfectly fine at my end. Can you add break point in your code and step run the code to see if the below formula is converting the date from dd.mm.yyyy format to mm/dd/yyyy format.
Also note that the input in the text box has to be in dd.mm.yyyy format only...
Please Login or Register to view this content.
Last edited by vivek.jain; 09-20-2013 at 07:59 AM.
I have tried many ways, but our Czech dd.mm.yyyy date format is probably cursed. I have not managed to get it working.
It gets stuck always here:Please Login or Register to view this content.
can you please upload sample file..
in the meanwhile.. try this..
Criteria1:=">=" & cdate(replace(range("A2"),".","/")), Operator:=xlAnd, Criteria2:="<=" & cdate(replace(range("B2"),".","/"))
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
I am lost with this, would appreciate your help.
Here is the file: test1.xlsm
Here is one possible solution in case anyone is interested: Book1.xlsm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks