جلوگیری از تغییر قالب بندی خودکار تاریخ

Question

در یک کاربرگ، کارول سلولی دارد که به "حسابداری" فرمت شده است. اگر شخصی به طور تصادفی تاریخ (mm/dd/yy) را در آن سلول وارد کند، اکسل به طور خودکار قالب بندی سلول را تغییر می دهد تا تاریخ را به درستی نشان دهد. با این حال، اگر او دوباره سعی کند مقدار دلاری را در آن سلول وارد کند، به فرمت "حسابداری" بر نمی گردد. سلول در قالب تاریخ باقی می ماند. اگر کاربر خطا را ببیند و آن را تصحیح کند خوب است، اما بارها این اتفاق در قالبی با متن "boilerplate" رخ می دهد و الگو بدون دسترسی به سلول های قالب بندی محافظت می شود. کارول نمی پرسد آیا کسی ایده ای در مورد اینکه چرا این اتفاق می افتد و چگونه آن را اصلاح می کند، دارد.

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

شکل 1. گزینه های پیشرفته در کادر محاوره ای Excel Options.

1. کادر محاوره ای Excel Options را نمایش دهید. (در اکسل 2007 روی دکمه Office کلیک کنید و سپس روی Excel Options کلیک کنید. در اکسل 2010 یا نسخه جدیدتر، تب File را در نوار نمایش داده و سپس روی گزینه ها کلیک کنید.)
2. در سمت چپ کادر گفتگو روی Advanced کلیک کنید.
3. به انتهای گزینه های پیشرفته بروید. (شکل 1 را ببینید.)
4. مطمئن شوید که گزینه Transition Formula Entry انتخاب شده است.
5. روی OK کلیک کنید.

این گزینه خاص باعث می شود اکسل اطلاعات وارد شده را به همان روشی که Lotus 1-2-3 انجام داد، ارزیابی (تجزیه) کند. این بدان معنی است که تاریخ ها دیگر به عنوان تاریخ تجزیه نمی شوند، بلکه به عنوان یک فرمول تجزیه می شوند. بنابراین، اگر شخصی 11-16-13 را در یک سلول وارد کند، آنگاه به صورت "یازده منهای شانزده منهای سیزده" تجزیه می شود و در خانه به صورت -18 نشان داده می شود. از آنجایی که به عنوان تاریخ تجزیه نشده است، فرمت حسابداری به صورت دلخواه با سلول مرتبط می ماند.

اگرچه این رویکرد دارای اشکالاتی است. از آنجایی که اکسل متعاقباً هر ورودی را طبق قوانین لوتوس تجزیه می کند، کاربران شما ممکن است به این نتیجه برسند که کاربرگ شما به درستی کار نمی کند زیرا از قوانین مشابهی پیروی نمی کند که کاربرگ های دیگر اکسل از آن پیروی می کنند. به همین دلیل اشاره کردم که این رویکرد ممکن است برای کاربرگ های ساده قابل قبول باشد. اگر کاربرگ شما واجد شرایط باشد، باید تصمیم بگیرید.

اگر نمی خواهید نحوه تجزیه و تحلیل را تغییر دهید، بهترین روش ممکن است اضافه کردن چند کنترل کننده رویداد به کاربرگ خود باشد. برای مثال، می توانید یک کنترل کننده رویداد داشته باشید که به مکان ورود داده ها نگاه می کند و اطمینان می دهد که هرگونه تغییر در آن سلول ها قالب بندی مورد نظر را حفظ می کند.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToCheck As Range
    Set rngToCheck = Range("E2")
    If Intersect(Target, rngToCheck) Then
        rngToCheck.NumberFormat = _
          "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"
    End If
End Sub

در این مثال، سلولی که می خواهید قالب حسابداری را در آن حفظ کنید، E2 است که به متغیر rngToCheck اختصاص داده شده است. اگر می خواهید قالب را در محدوده سلولی دیگری اعمال کنید، فقط خط تخصیص را تغییر دهید.

اگر کمی انعطاف پذیری بیشتری می خواهید، می توانید از مجموعه متفاوتی از کنترل کننده های رویداد استفاده کنید. به عنوان مثال، مثال های زیر از رویدادهای SelectionChange و Change شیء Worksheet استفاده می کنند. آنها به چیزی منجر می شوند که آنقدر به یک قالب خاص فشار نمی آورد، اما از تغییر قالب بندی سلول نسبت به آنچه قبلا بود جلوگیری می کند. بنابراین، این رویکرد از هر قالب بندی محافظت می کند، نه فقط یک قالب حسابداری را اعمال می کند.

Dim nFormat As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToCheck As Range
    Set rngToCheck = Range("E2")
    If Intersect(Target, rngToCheck) Then
        rngToCheck.NumberFormat = nFormat
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    nFormat = Target.NumberFormat
End Sub

کنترل کننده رویداد SelectionChange ابتدا فعال می شود و قالب موجود را در متغیر nFormat تنظیم می کند. سپس Change event handler فعال می شود و قالب بندی را به حالت اصلی برمی گرداند.

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

شکل 2. کادر محاوره ای Data Validation.

1. سلول یا سلول هایی را که می خواهید قالب بندی آنها را اعمال کنید انتخاب کنید.
2. تب Data روبان را نمایش دهید.
3. بر روی ابزار Data Validation در گروه Data Tools کلیک کنید. اکسل کادر محاوره ای Data Validation را نمایش می دهد.
4. با استفاده از لیست کشویی Allow، Custom را انتخاب کنید. (شکل 2 را ببینید.)
5. در کادر Formula عبارت زیر را تایپ کنید: =CELL("format",B2)="C2"
6. سایر تنظیمات اعتبارسنجی داده را به دلخواه تنظیم کنید.
7. روی OK کلیک کنید.

فرمول (مرحله 5) قالب بندی سلول را بررسی می کند و ورود بر اساس آن قالب بندی را مجاز یا ممنوع می کند. در فرمول ذکر شده، فرمت C2 نام داخلی قالب حسابداری است. شما به راحتی می توانید کدهای موجود در فرمول را به فرمت های دیگری مانند ",2"، "C2"، "C0"، "C2-" یا "C0-" بسته به ترجیح خود تغییر دهید. ساده ترین راه برای فهمیدن اینکه از چه قالبی باید استفاده کنید این است که قبل از اعمال قانون اعتبارسنجی داده، یک سلول را به دلخواه قالب بندی کنید. (به عنوان مثال، فرض کنید قالب بندی را روی سلول L13 اعمال می کنید.) سپس می توانید از این فرمول در سلول دیگری استفاده کنید تا ببینید اکسل معتقد است چه فرمتی را اعمال کرده اید:

=CELL("format",L13)