none
Positive numbers RRS feed

  • Question

  • Hello all,

    I have this code but i need to update it.

    I need that in column "Q" and "R" I can only be able to put positive numbers.

    Is that possible? 

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Long
        r = Range("B" & Rows.Count).End(xlUp).Row - 1
        If Target.Count > 1 Then Exit Sub
        If Not Intersect(Range("Q6:Q" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            Target.Offset(0, -1).Value = Target.Offset(0, -1).Value + Target.Value
            r = Target.Row
            Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Target.Value
            Application.EnableEvents = True
        End If
        If Not Intersect(Range("R6:R" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            Target.Offset(0, -2).Value = Target.Offset(0, -2).Value - Target.Value
            Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = -Target.Value
            Application.EnableEvents = True
        End If
    End Sub

    Thank you

    JPMR


    Jose

    Wednesday, July 10, 2019 2:15 PM

Answers

  • Instead of using code, use Data Validation for this.

    Select the cells in columns Q and R in which the user can enter numbers.

    On the Data tab of the ribbon, click Data Validation.

    Select 'Whole Number' or 'Decimal' from the Allow dropdown, depending on whether the user should be able to enter whole numbers only or numbers with a fractional part.

    Select 'greater than' from the Data dropdown.

    Enter 0 in the Minimum box.

    If you wish, activate the Error Alert tab and specify the text of the message that will appear if the user enters an invalid value.

    Click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JPMR Monday, July 15, 2019 7:55 AM
    Thursday, July 11, 2019 7:53 AM
  • You may try something like this...

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Long
        r = Range("B" & Rows.Count).End(xlUp).Row - 1
        
        If Target.Count > 1 Then Exit Sub
        
        If Not Intersect(Range("Q6:Q" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            If Target >= 0 Then
                Target.Offset(0, -1).Value = Target.Offset(0, -1).Value + Target.Value
                r = Target.Row
                Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Target.Value
            Else
                MsgBox "Negative number is not allowed in this column.", vbExclamation, "-ve Numbers Not Allowed!"
                Application.Undo
                GoTo Skip
            End If
        End If
        
        If Not Intersect(Range("R6:R" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            If Target >= 0 Then
                Target.Offset(0, -2).Value = Target.Offset(0, -2).Value - Target.Value
                Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = -Target.Value
            Else
                MsgBox "Negative number is not allowed in this column.", vbExclamation, "-ve Numbers Not Allowed!"
                Application.Undo
                GoTo Skip
            End If
        End If
        
    Skip:
    Application.EnableEvents = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by JPMR Monday, July 15, 2019 7:55 AM
    Thursday, July 11, 2019 12:31 PM

All replies

  • Hi Jose,

    Welcome to the Microsoft Office for IT Professionals Excel forum. To help you better resolve the problem, I will move the thread to Excel for Developers Forum. You may get more helpful replies there.

    Thanks for your kind understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, July 11, 2019 6:28 AM
  • Instead of using code, use Data Validation for this.

    Select the cells in columns Q and R in which the user can enter numbers.

    On the Data tab of the ribbon, click Data Validation.

    Select 'Whole Number' or 'Decimal' from the Allow dropdown, depending on whether the user should be able to enter whole numbers only or numbers with a fractional part.

    Select 'greater than' from the Data dropdown.

    Enter 0 in the Minimum box.

    If you wish, activate the Error Alert tab and specify the text of the message that will appear if the user enters an invalid value.

    Click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JPMR Monday, July 15, 2019 7:55 AM
    Thursday, July 11, 2019 7:53 AM
  • You may try something like this...

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Long
        r = Range("B" & Rows.Count).End(xlUp).Row - 1
        
        If Target.Count > 1 Then Exit Sub
        
        If Not Intersect(Range("Q6:Q" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            If Target >= 0 Then
                Target.Offset(0, -1).Value = Target.Offset(0, -1).Value + Target.Value
                r = Target.Row
                Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Target.Value
            Else
                MsgBox "Negative number is not allowed in this column.", vbExclamation, "-ve Numbers Not Allowed!"
                Application.Undo
                GoTo Skip
            End If
        End If
        
        If Not Intersect(Range("R6:R" & r), Target) Is Nothing Then
            Application.EnableEvents = False
            If Target >= 0 Then
                Target.Offset(0, -2).Value = Target.Offset(0, -2).Value - Target.Value
                Cells(Target.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = -Target.Value
            Else
                MsgBox "Negative number is not allowed in this column.", vbExclamation, "-ve Numbers Not Allowed!"
                Application.Undo
                GoTo Skip
            End If
        End If
        
    Skip:
    Application.EnableEvents = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by JPMR Monday, July 15, 2019 7:55 AM
    Thursday, July 11, 2019 12:31 PM