Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation (2002)

    I am dumping a simple database into a worksheet and Excel is ignoring data validation for the cells. I would like a message box to appear when there is unexpected data. Is there anyway around this? Thanks

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Validation (2002)

    Depends a bit how you are doing this, but one of the problems with data validation is that if another cell is pasted over a cell containing validation, validation is lost. Try resetting the validation rules after each import, by copying them from a cell outside the import area, or by macro.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2002)

    Could you define what "dumping" is? There are a number of different possibilities depending on the process you are using.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2002)

    An Iconics TWXReport program grabs logged data from a TWXSQLServer and writes it to a specified worksheet in Excel. Excel doesn't have to be running at the time. TWXReport will open Excel long enough to write the data and save the worksheet as a new file. It then closes Excel. When I look at a saved worksheet that has been written to and I've formatted for data validation, there is data resting in cells that defy the validation rules. The cell still contains the same data validation formatting. Fpr example I can simulate what is happening by validating A1 to accept only the number 8 but can enter a 9 into A1 if I set A1=B1 and enter 9 into B1. Thanks for the help

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Validation (2002)

    Data Validation is deisgned to "guide" manual user data entry, so doesn't fit what you want it to do.You might think about conditional formatting which set cell fill color red if the valid criteria are not met, or write a macro that reviews the fields and provides a messagebox for each invalid data cell.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2002)

    Can a Macro be set to run given the condition of a cell?

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation (2002)

    Not a macro, but the change event can test for out of range. Right-click on the sheet tab and select View code. Use the left drop-down to choose Worksheet & the right drop-down to choose Change. Now you can check target for out-of-range and change its format.

    Before you do that, I would try custom number formatting. The conditions don't have to be positive, negative, or zero.

    How does TWXReport open Excel and write the data? What are your Validation rules? Tomorrow I can try to simulate your problem.
    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2002)

    I don't know enough about TWXReport to know how it opens and writes to Excel. I do have the option of automatically printing the file that is written to before it closes. For an example, I've attached a spreadsheet that I formatted for data validation and has been written to by TWXReport. The logged data is in column H. The report runs every 15 minutes and writes the length of time a sample pump is on per day in 3 minute intervals. The sample pump runs continuously so I want to use this value as an alert if the logging program malfunctions. It naturally should increase throughout the day. If a message box could pop up if the logged value is not increasing before Excel can close, it would alert the operator to the problem. Thanks
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation (2002)

    I think that I have a solution: I have added a BeforeClose event to your workbook and attached it. The code is below; you will want to change the error message. To insert the code in your workbook:
    1) Open the workbook and press Alt F11 to get to the IDE.
    2) In the progect explorer window, double click on ThisWorkbook.
    3) Copy the code below, paste it into Word, and copy it from Word (this gets rid of the HTML formatting)
    4) Paste the code into the IDE code window.
    5) Close and save the workbook
    6) Test it. HTH --Sam
    <pre>Option Explicit

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long
    Dim n As Long
    Dim sngMin As Single
    With Sheets("Logging_Check")
    sngMin = .Range("H8")
    With .UsedRange
    n = .Row + .Rows.Count - 1
    End With
    For i = 9 To n
    If .Cells(i, 8) > sngMin Then ' OK
    sngMin = .Cells(i, 8)
    Else
    MsgBox "Pump is Failing!", vbInformation, _
    "Logging Error"
    Exit Sub
    End If
    Next i
    End With
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2002)

    Fantastic! It works fine. FYI Excel still closes out on its own after being written to with invalid data but the Message Box remains onscreen, waiting for acknowledgment. That is better yet. Thanks again!

Posting Permissions

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