Data Validation with Macro

[madchemist]

I have a macro that inserts data from a userform into a row and also sets validation on several cells. One cell that corresponds to a text box on the userform (so users can enter whatever they want) has validation set to make sure it is a decimal between 1.01 and 10.99.

The validation works find if I try and change the cell, but if the user enters an initial value in the cell that is out of the range, it gives no error....

Is it possible to make the macro validate the data? Or do I need to restrict the values that can be entered on the userform somehow, perhaps using a drop down menu or something? I would prefer to not use a drop down menu, as there is a lot of data and would be time consuming scrolling...

Thanks!

 

[Von Pookie]

One suggestion is to make sure the macro is setting up the validation *before* entering the numbers.

Other than that, you can write code for the userform so that the user must enter a correct number to begin with. One quick example:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Select Case Me.TextBox1.Value
    Case Not 1.01 To 10.99 'if the value entered is 1.01-10.99
        'put the value in Sheet1-A1
        Sheets("Sheet1").Range("A1").Value = Me.TextBox1.Value
    Case Else 'if the value entered is anything else
        'display a message
        MsgBox "You must enter a decimal between 1.01 and 10.99.", vbExclamation
        'clear the entry from the box and keep the cursor in the textbox
        Cancel = True
        With Me.TextBox1
            .Value = ""
            .SetFocus
        End With
End Select

End Sub