CODEWITHSUNDEEP
vbaexcelexcel-2013
Keyur Shah
Keyur Shah

Reputation: 27

Restrict Excel Cell Input Value

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.

Upvotes: 0

Views: 11195

Answers (2)

cyboashu
cyboashu

Reputation: 10433

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.

Upvotes: 2

PhpLou
PhpLou

Reputation: 440

I am not an excel programmer, however I have found this a few weeks ago while looking at an excel file
You have to go to data tab then Data Validation then you put your criteria
You can even put an input message and an error message

Upvotes: 1

Related Questions