Message Box popup if cell is negative

[jrisebo]

Im trying to get some code to work for a range of cells being negative. Lets say A1 to A5 are the cells to check, I want a message box to pop up saying "Hey, one of your values is negative" The values wont be data entry, but a cell that is calculated based on some other entry.

 

[Sektor]

Sub FFF()
  If Application.Min([A1:A5]) < 0 Then MsgBox "One of the values is negative", vbExclamation
End Sub

 

[jrisebo]

Thanks this worked.

Anyway to only get it to show up once per opening of the file, i.e. "disable until nextime I open file" checkbox?

 

[Skyybot]

Place in ThisWorkbook code module. - Copied from @Sektor

Private Sub Workbook_Open()
 If Application.Min([A1:A5]) < 0 Then MsgBox "One of the values is negative", vbExclamation
End Sub

 

[jrisebo]

One more thing on this, I dont want this code to run until a value is entered into a specific cell, i.e. C1 cell. So the user can do what they want, and then when they type in the C1 cell, the code becomes active.

 

[SeniorNewbie]

ok - you're describing an event in a defined Sheet. So no Workbook_Open event of course. Selection_Change ist better. And you have to check two conditions: cell.value <>"" and cell.value<0. Try this in your sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
'If Target.Row = 10 And Target.Column < 6 Then    'that's A10:E10
If Target.Address = "$C$10" Then
    For i = 1 To 5
        If Cells(10, i) <> "" And Cells(10, i) < 0 Then
            MsgBox "Alert in cells(10," & i & ")"
        End If
    Next i
End If
End Sub

Good luck!

Newbie sen.