PDA

View Full Version : Date on Userform



bdsii
01-24-2010, 08:50 AM
Hello....I have a sample spreadsheet and code attached. I want to enter transactions with a Transaction Date but the problem is the date has to be in MMM-YYYY format without the day portion. I would prefer to have a dropdown to select that is populated but want it formatted as a date so that the data can be sorted in order like Jan-2010, Feb-2010, Mar-2010 instead of as text alphabetically.

On the sample spreadsheet I have used a text box that can be used instead of a drop down but only if I can have code to format it into the correct format upon cell exit. I have this code for the number for the Amount and would like something similar for dates but have not figured out the coding to do that yet.

Can anyone point me in the correct direction for solving this date issue ?

Thanks for your help,

bdsii

wjdb
01-24-2010, 09:43 AM
I think the code you need to solve your date problem is
Format(Me.TransMonthYear.Value, "mmm-yyyy")

I was also having a problem with the number validation so have tidied up your code a bit - see attached book.

Let me know if you need more help.

xld
01-24-2010, 10:53 AM
You don't validate the amount for just 2 dec places, I put in many more.

bdsii
01-24-2010, 11:40 AM
Thanks guys for the help....

xld - I am not sure I understand your statement, I am little slow :-)

wjdb - thanks for the code....I had thought of the dropdown but it works better than I thought it would.

Is there a way to throw an error if the TransNumber and/or the Amount is blank when exiting the cell ?

Also, with the code you provided, if the spreadsheet cell is not formatted for a date in the format of MMM-YYYY then the code does not force that format. It comes out MMM-YY. Any ideas to force it to be formatted correctly even if the cell has not been formatted ?

Thanks !

bdsii
01-24-2010, 11:41 AM
Thanks guys for the help....

xld - I am not sure I understand your statement, I am little slow :-)

wjdb - thanks for the code....I had thought of the dropdown but it works better than I thought it would.

Is there a way to throw an error if the TransNumber and/or the Amount is blank when exiting the cell ?

Also, with the code you provided, if the spreadsheet cell is not formatted for a date in the format of MMM-YYYY then the code does not force that format. It comes out MMM-YY. Any ideas to force it to be formatted correctly even if the cell has not been formatted ?

Thanks !

xld
01-24-2010, 12:30 PM
Thanks guys for the help....

xld - I am not sure I understand your statement, I am little slow :-)

I was referring to the response, that solution allows inputting of an amount with more than 2 dec places, and rounds it. I just think that currency should be validated on input, not allowing more than 2 dec places.