Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    11

    Question Unanswered: Reset formula after input error

    Okay, here's a tricky one for you. I am developing a visit schedule for patients in a clinical trial. Each visit has to take place within a certain window after the first visit, so I've set up a spreadsheet to calculate, after the user inputs the first visit date, all of the expected subsequent visit dates. (These are all dimmed and italicized, to indicate that these visits have not yet taken place.)

    Easy.

    As each visit takes place, the user is to input the ACTUAL visit date, in the box where the expected visit date is already calculated. I've got a macro working so that it changes the formatting to black, un-italicized font to indicate that this is the actual visit date. I also have conditional formatting to highlight the date if it does not fall within the correct visit window.

    This is the problem: If the user inputs an actual date over the calculated expected date, and then decides that it was an error (like they put it in a cell for the wrong patient or something) and they press delete, it deletes both the wrong actual date AND the formula. I want to know how to make the default value of the cell the calculated formula. Basically, how do you protect a formula, but still allow user input?

    Thanks in advance for your help.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Just a thought or two...

    One way would be to use vba code to recopy the formula. But this could get messy.

    Another is to use VBA USerForms, to allow selection dates, and then when they click OK on the UserForm, bring up a "Are you sure this is correct?" (including list of patient, date, time, etc., UserForm to verify before entry acutally takes place.

    The second option will take a little more time, but I think it will be easier to maintain control.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Shades: One way would be to use vba code to recopy the formula. But this could get messy.

    You can use a Worksheet Change Event to detect changes to the worksheet and take action, like undoing an entry or re-entering a formula based on the user entry. As Shades points out this can get messy with the validation requirements. This is a very basic example
    Code:
     
    ' Code is added to the worksheet module for each 
    ' worksheet the action is needed on
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim bIsValid As Boolean
        Dim msg As String
        
        bIsValid = False
        cellVal = Range(Target.Address)
        If IsDate(cellVal) Then
          bIsValid = (cellVal >= Date And cellVal <= Date + _
                      30)
          If Not bIsValid Then _
          msg = "The date must be equal or greater than todays date," & _
                " and Less than 30 days from today. Undoing Entry!"
        Else
          bIsValid = False
          msg = "You must enter a date value. Undoing Entry!"
        End If
        If Not bIsValid Then
          MsgBox msg
          Range(Target.Address).FormulaR1C1 = "=Today()+10"
        End If
    End Sub


    /
    ~

    Bill

Posting Permissions

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