0

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.

Keyur Shah
  • 27
  • 1
  • 1
  • 6

2 Answers2

2

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.

cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • Thank you so much Cyboashu. Can I force user to enter only numeric value? Like I have seen some PDF files that only allows you to type Numeric Value. If you try to type text or any alphabets it wont let user type text. You help is much appreciated... – Keyur Shah Aug 22 '14 at 01:54
  • You are welcome. Yes, it possible to build a control which will disable non-numeric keys for particular cells. But, my suggestion is to avoid doing it in spreadsheet because it will slow your input process. Also, a whole lot code will be required to disable PASTE on those cells. Unless, its very critical for your spread sheet, i don't recommend to tweak the normal input behavior of the cells. – cyboashu Aug 22 '14 at 02:38
  • You can look into this [link]( http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell), it explains about catching key press on a cell. That way you could disable alphabets. – cyboashu Aug 25 '14 at 05:08
1

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

PhpLou
  • 430
  • 3
  • 16
  • Hi PhpLou...I have tried that and the thing you are saying is not what I want. I want to make that cell in a way that no one can enter any alphabets. It wont let user type anything. Thanks for your reply – Keyur Shah Aug 22 '14 at 02:50