Reset formula after input error

bruch04 · #1 (**permalink**) 12-22-04, 13:27

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.

shades · #2 (**permalink**) 12-23-04, 10:06

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.

savbill · #3 (**permalink**) 12-24-04, 00:04

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

/

Internet Services

Web Development

Digital Marketing

Consumer Tech