check after insert a value in a cell.

[natasha85]

Hi folks! I need to check after insert a value in a particular cell if it is empty, numeric and <=>0
Thanks in advance,
Natalya

 

[nemmi69]

using VBa?

 

[natasha85]

using VBa?

Yes, it should be better... or another way is good too... :D

Naty

 

[Joe4]

Are these value being inserted manually?
What cell do you want to check?
What do you want to happen in each instance?

 

[natasha85]

Are these value being inserted manually?
What cell do you want to check?
What do you want to happen in each instance?

Are these value being inserted manually? YES
What cell do you want to check? 10 cell B5, B7, B9, B11 and so on...
What do you want to happen in each instance? after I insert a value in these cell...and I exit from the cell...I want to check it and I want I cant go to another until I put a number value <>0 and not null.
Thanks,
Naty

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Set rng = Range("B5:B23")

'   Only run if one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if value in designated range
    If Not Intersect(Target, rng) Is Nothing Then
'       Only run if odd row number updated
        If Application.WorksheetFunction.IsOdd(Target.Row) Then
'           Check to make sure entry is a numeric entry
            If IsNumeric(Target.Value) And (Target.Value <> 0) Then
'               Do nothing, entry is fine
            Else
'               Clear entry and display warning
                Application.EnableEvents = False
                Target.ClearContents
                Application.EnableEvents = True
                MsgBox "Entry must be non-zero numeric entry", vbOKOnly, "ERROR!"
            End If
        End If
    End If

End Sub

This code will run automatically upon data entry. See if it does what you want.

 

[natasha85]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Set rng = Range("B5:B23")

'   Only run if one cell updated
    If Target.Count > 1 Then Exit Sub
    
'   Only run if value in designated range
    If Not Intersect(Target, rng) Is Nothing Then
'       Only run if odd row number updated
        If Application.WorksheetFunction.IsOdd(Target.Row) Then
'           Check to make sure entry is a numeric entry
            If IsNumeric(Target.Value) And (Target.Value <> 0) Then
'               Do nothing, entry is fine
            Else
'               Clear entry and display warning
                Application.EnableEvents = False
                Target.ClearContents
                Application.EnableEvents = True
                MsgBox "Entry must be non-zero numeric entry", vbOKOnly, "ERROR!"
            End If
        End If
    End If

End Sub

This code will run automatically upon data entry. See if it does what you want.

Ohhhh Thanks....it works like a charm...the only thing I want more..... is to force the user to correctly fill the cell..
the user cant go to other cells until insert a correct value...

 

[Joe4]

the only thing I want more..... is to force the user to correctly fill the cell..
the user cant go to other cells until insert a correct value

Hmmm... that part is a bit tricky. I don't know that you can "force" a user to make an entry. We can erase the current entry if certain conditions are not met though.
What we can do is something like the following:
- when they make an entry in one of these cells, verify that the previous one is populated (i.e. if they are entering in B11, make sure B9 is populated first)
- make sure some other columns in the same row already have an entry

Does either of those options work for you?
If so, please provide any necessary details (like for the second one, which columns in that row need to be populated first).

 

[nemmi69]

Something like this?


This code goes on the worksheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        ' Display a message when one of the designated cells has been
        ' changed.
    If IsEmpty(Target.Value) Then
        MsgBox Target.Address & "is Empty"
        Exit Sub
    End If
    If IsNumeric(Target.Value) And (Target.Value) < 0 Then
        MsgBox Target.Address & "is less than zero"
        Exit Sub
    End If
    If IsNumeric(Target.Value) And (Target.Value) > 0 Then
        MsgBox Target.Address & "is greater than zero"
        Exit Sub
    End If
    If IsNumeric(Target.Value) And (Target.Value) = 0 Then
        MsgBox Target.Address & "is equal to zero"
        Exit Sub
    End If
        MsgBox "Cell " & Target.Address & " has changed."
       
    End If
End Sub