p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Data Validation and format (http://p2p.wrox.com/showthread.php?t=60821)

zone August 4th, 2007 10:46 PM

Data Validation and format
 
1) How could I force user to enter data (Date) in this column (Cell-) ACC-DATE) like exact same format.
2) If user keep blank in first column they will be prompt to put data in ACC-DATE cell in dd/MON/YY format.

ACC_DATE BATCH_NAME Amount1 total1 total2
-------- --------- ------- ------ -----
02-MAY-07 Batch 1 10.00 10.00 10.00
03-JUN-07 Batch 2 20.00 20.00 20.00
10-JUL-07
20-AUG-07

3) Users are not allowed to enter data into next cell/row until they enter date.
Kind of message box type will be a great to do I guess.

(Date can not be null and can not be other then dd/MON/YY format.)

Feedback’s are greatly appreciated.


**How can I lock the total1 and total2 column using macro/VBA?
Thanks



FullSquat August 5th, 2007 07:02 PM

In the worksheet‘s column to be used for the dates highlight the rows to be used for the dates. Create a range name for the area. Then create the custom date format dd-mmm-yy for the range.
In the worksheet’s SelectionChange event use the following code:

    Dim rcell As Range
    Dim dateRange As Range
    Dim msg As String
    Set dateRange = Range("TheDates")
    Set rcell = ActiveCell

    msg = "Row contents not a date" & vbCrLf & "or the row is empty"

    If Union(dateRange, rcell).Address = dateRange.Address And _
    Union(Range("A6"), rcell).Address <> Range("A6").Address Then

        If Not IsDate(rcell.Offset(-1, 0)) Or IsEmpty(rcell.Offset(-1, 0)) Then
            MsgBox msg, vbCritical, "Enter a Date"
            rcell.Offset(-1, 0).Select
        End If

  End If


A6 is the first cell of the date range.
If the person does not enter a date or the cell is empty, when the person moves to the next row a message dialog box will appear. It’s not perfect but it should work in most cases.





zone August 7th, 2007 02:53 PM

Tanks for your reply.
Now it works only for date column, which is great.

If I skip a cell in a date column and point into another cell below then get error message.
1) I like to see error message when user attempt to type (not just click) something when date cell is empty and not date format.

2) I am not getting error message when skip date and type into next columns (Batch- Name. amount1 etc). (Reason behind, because my range only ACC-Date column?)
Do I have to write similar function for other 4 columns too?

Thanks for your valuable time and support….




All times are GMT -4. The time now is 11:50 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.