Restrict Excel Cell Input Value

Question

I have Excel Sheet. I have locked and Protect other cells. I have 2 cells that required user input and both are currency Datatype. If I input text in those cells it messes up calculation so I would like to format those cells in a way that if anyone type text or sentence it will give error and do not affect calculation and ask for number input.

I am new to Excel programming so it would be hard for me first time.

Answer 1

Tip : Data Validation is a very weak control mechanism. When you copy and paste a value in a cell, all data validations are by passed.

For the sake of a solution, lets assume that the currency cells are A1 and B1 on sheet1.

Goto the code of sheet1 in VBE and write a macro, something like this

            Dim lOldVal As Long 'Sheet Module level variable

            Private Sub Worksheet_Change(ByVal Target As Range)

                If Target.Address = "$A$1" Or Target.Address = "$B$1" Then

                    If Not IsNumeric(Target.Value) Then
                        MsgBox "Only numeric values allowed.", vbInformation, "Foo"
                        Application.EnableEvents = False
                        Target.Value = lOldVal
                        Application.EnableEvents = True
                    End If

                End If

            End Sub

            Private Sub Worksheet_SelectionChange(ByVal Target As Range)
                  If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
                    lOldVal = Target.Value
                  End If
            End Sub

change $A$1 and $B$1 with your actual cell address.