Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Thanked 0 Times in 0 Posts

    asking too much of data validation?

    dates are entered daily in column a. A new file is opened
    every month (copied from a template spreadsheet that I've set up).
    In data validation (settings tab) I have allow: dates between say 1/1/01 and 31/1/01.
    But on copying over the template every month, I've got to amend the dates to reflect the 1st and last date of the current month.
    Is it possible to make the template more efficient, so that on being copied over, it will automatically only accept dates from the current month.

    Note: I know I could use =MONTH(a1)=MONTH(NOW()) in data val >settings, but once it gets to the end of the month, users can then continue entering dates for say the 1st,2nd,3rd etc. of the following month, if they are too lazy to open a new worksheet.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Dublin, Ireland, Republic of
    Thanked 0 Times in 0 Posts

    Re: asking too much of data validation?

    The following procedure will enter the value of the 1st day of the month that the work is first opened in Cell A1 of Sheet1. When the workbook opens it checks the value in A1 and if the cell is empty in inserts the date of the first day of the current month. That way (providing nobody removes the value from cell A1) the value will remain at the set date. Note that this procedure must be placed the ThisWorkbook object and not in a general module.<pre>Private Sub Workbook_Open()
    If Range("A1").Value = "" Then
    Range("A1").Value = DateValue("01/" & Month(Now()) & "/" & Year(Now()))
    End If
    End Sub</pre>

    For Data Validation you then select Allow date, Between Start Date = A1 and type the following formula in the End date box.
    <pre> =DATE(YEAR(A1),MONTH(A1)+1,0)</pre>

    - that returns the date of the last day of the month in A1.
    You can of course change all references to whatever your chose cell is. It would probably be a good idea to hide it. If you wish to protect the cell, the above procedure would require adjustment.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts