VAB, Message if Positive Values

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
I am dealing with a dataset that has columns that for the most part should only have negative values entered in them, when you click away from the sheet or you're closing the file and there are positive numbers entered in the predefined columns, I want a warning message to come up (it should allow you to proceed).

your help is appreciated.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
Hi Willmondo,

This will check for possitive values in Column A

Code:
Sub CheckPositves()

Dim lrow As Long
Dim mymsg As String


lrow = Range("A" & Rows.Count).End(xlUp).Row


For i = 1 To lrow
    
    If Cells(i, 1).Value > 0 Then
        mymsg = MsgBox("There is positive value in Row: " & i & " in Column A" & vbCr & _
                        "Would you like to continue?", vbInformation + vbYesNo, "Positive value")
        If mymsg = vbNo Then
            Cells(i, 1).Activate
            Exit Sub
        End If
    End If
Next i


End Sub
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Thanks Fredlo,

I have multiple columns that I need this VBA to check for positive values, where should I indicate which ones they are?
Also, it errors out when I get the warning message if I click on “No”.

Can you please take another stab at it?

I’m grateful for your time and help!
 

Mahesh_P

New Member
Joined
May 24, 2012
Messages
47
Try :

Code:
Sub Mtest()
Dim LR As Long, PR As Long, s As Long, m As Long
Dim Temp As String
LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
PR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For s = LR To 1 Step -1
For m = PR To 1 Step -1
If Application.WorksheetFunction.CountIf(Cells(s, m), ">0") = 1 Then
Temp = MsgBox("Positive Value Found in" & Cells(s, m).Address & "  Do u want to Continue ?", vbYesNo, "Warning")
If Temp = vbYes Then
Cells(s, m).Select
Else
Exit Sub
End If
End If
Next m
Next s
Application.ScreenUpdating = True
End Sub
 

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
Thanks Mahesh,

now it errors out when I click yes to continue,

the error I'm getting is a run-time error 1004 "select method of range class failed
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top