ورود محدود تاریخ های قبلی

Question

اگر از قابلیت های اعتبارسنجی داده اکسل استفاده می کنید، می توانید آنچه را که به یک سلول می رود، بر اساس محتویات سلول دیگر محدود کنید. به عنوان مثال، شما به راحتی می توانید آنچه را که به سلول A2 می رود بر اساس تاریخی که در سلول A1 است محدود کنید. این مراحل را دنبال کنید:

شکل 1. تب تنظیمات کادر محاوره ای Data Validation.

1. سلول A2 را انتخاب کنید.
2. روی تب Data روبان کلیک کنید.
3. روی Data Validation در گروه Data Tools کلیک کنید. اکسل کادر محاوره ای Data Validation را نمایش می دهد.
4. مطمئن شوید که تب تنظیمات نمایش داده می شود. (شکل 1 را ببینید.)
5. با استفاده از لیست کشویی Allow، تاریخ را انتخاب کنید.
6. با استفاده از لیست کشویی Data، Greater Than یا Equal To را انتخاب کنید.
7. در کادر تاریخ شروع، =A1 را وارد کنید . این به اکسل می گوید که تاریخ باید بزرگتر یا مساوی با هر تاریخی باشد که در سلول A1 است.
8. روی OK کلیک کنید.

اکنون، هر زمان که بخواهید تاریخی را در سلول A2 وارد کنید که زودتر از تاریخ سلول A1 است، اکسل یک پیغام خطا نشان می دهد و اجازه وارد کردن تاریخ را نمی دهد.

با این حال، وقتی می خواهید تاریخ هایی را که می توان در سلول A1 وارد کرد محدود کنید، چه اتفاقی می افتد؟ به عنوان مثال، اگر تاریخ 4/1/20 را در سلول A1 قرار دهید، و می خواهید مطمئن شوید که تاریخ بعدی وارد شده در A1 زودتر از 4/1/20 نباشد. اگر تاریخی مانند 4/15/20 را در سلول A1 قرار دهید، مشکلی نیست، اما دفعه بعد که تاریخ را در سلول A1 وارد می کنید، آن را زودتر از 4/15/20 نمی خواهید. به عبارت دیگر، می خواهید مطمئن شوید که سلول A1 فقط می تواند تاریخ های دیرتر از تاریخ فعلی را در A1 بپذیرد.

این کمی چسبنده است. اگر مراحل بالا را دنبال کنید اما سلول A1 را در مرحله 1 انتخاب کنید، اعتبارسنجی داده کار نخواهد کرد. چرا؟ از آنجایی که تاریخی که در سلول A1 وارد می کنید همیشه بزرگ تر یا مساوی با تاریخی است که در A1 وارد می کنید — اکسل هنگام انجام اعتبارسنجی داده ها با تاریخ قبلی در A1 مقایسه نمی شود.

تنها راه حل این مشکل استفاده از دو ماکرو است. ابتدا ماکرو زیر را در یک ماژول معمولی قرار دهید:

Sub Date_Validation()
    Dim dteDate As Date
    Dim strDate As String

    With Range("A1")
       Memo original date
        dteDate = CDate(.Text)
       Create date string
        strDate = Format(dteDate, "m/d/yy")
        With .Validation
           Delete old settings
            .Delete
           Set new data validation
            .Add _
              Type:=xlValidateDate, _
              AlertStyle:=xlValidAlertStop, _
              Operator:=xlGreaterEqual, _
              Formula1:=strDate

            .IgnoreBlank = False
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Invalid Date Entry"
            .InputMessage = ""
            .ErrorMessage = _
              "Date is older than the previous date (" & _
              dteDate & ")."
            .ShowInput = True
            .ShowError = True
        End With
    End With
End Sub

این ماکرو باید توسط یک ماکرو دیگر فراخوانی شود، این ماکرو در پنجره کد کاربرگ قرار می گیرد تا هر بار که تغییری در کاربرگ ایجاد می شود فعال شود:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target = Range("A1") Then Date_Validation
End Sub

نحوه کار این ماکروها واقعاً جالب است. از آنجایی که مورد دوم را در پنجره کد کاربرگ ها قرار می دهید، هر بار که تغییری در کاربرگ ایجاد می شود، فعال می شود. اگر سلول در حال تغییر A1 باشد، ماکرو Date_Validation اجرا می شود.

ماکرو Date_Validation تاریخ را از سلول A1 می گیرد و یک قانون اعتبارسنجی داده برای سلول می سازد. تمام کاری که انجام می دهد این است - یک قانون اعتبارسنجی داده را تنظیم می کند که اجازه نمی دهد تاریخی در سلول وارد شود که زودتر از تاریخ فعلی در سلول است.

زیبایی ماکرو این است که هنگامی که قانون اعتبارسنجی داده ها اجرا می شود، دفعه بعد که سلول A1 تغییر می کند، قانون اعتبارسنجی داده ها قبل از اجرا شدن رویداد Worksheet_Change فعال می شود. بنابراین، قانون اعتبارسنجی داده ها اطمینان حاصل می کند که فقط تاریخی بزرگتر از تاریخ فعلی را می توان وارد کرد. هنگامی که اعتبار سنجی داده ها پاک شد، پس از آن ماکرو از تنظیم مجدد قانون اعتبارسنجی داده ها مراقبت می کند، بنابراین با تاریخ تازه وارد شده مقایسه می شود.