Hello,
Hi this will probably be very simple for someome however i am having a nightmare day, all i want is a vba formual which will bring up a message box when a cell is showing a specific criteria.
Can anyone help?
Cheers
Ian
Ask a question
Report
Excel VBA Msg Box when cell meets criteria [Solved]
Iangough16  Latest answer on Mar 14, 2010 3:13am GMT
Read more
 Excel VBA Msg Box when cell meets criteria
 Excel VBA   Msg Box when cell meets criteria » HowTo  Excel
 Excel VBA  Selecting the next empty cell in a row » HowTo  Excel
 Excel / VBA  Detecting changes in cell » HowTo  Excel
 Excel vba  activate sheet from cell data (Solved) » Forum  Programming
 Adding a VBA CommandButton with its respective the code » HowTo  Excel
This code assumes that what you are looking for is in column A.
Private Sub Find_Criteria()
Dim i
Dim r
r = Range("A65536").End(xlUp).Row
i = 1
For i = i To r
If Range("A" & i) = "criteria" Then
MsgBox "Found" & " " & Range("A" & i).Address
End If
Next i
End Sub
If you are looking for certain criteria in a range of cells, then try something like this.
Private Sub Find_Criteria()
Dim i As Variant
Dim FindRange As Range
Set FindRange = Range("A1:K50")
For Each i In FindRange
If i = "criteria" Then
MsgBox "Found" & " " & i.Address
End If
Next i
End Sub
Private Sub Find_Criteria()
Dim i
Dim r
r = Range("A65536").End(xlUp).Row
i = 1
For i = i To r
If Range("A" & i) = "criteria" Then
MsgBox "Found" & " " & Range("A" & i).Address
End If
Next i
End Sub
If you are looking for certain criteria in a range of cells, then try something like this.
Private Sub Find_Criteria()
Dim i As Variant
Dim FindRange As Range
Set FindRange = Range("A1:K50")
For Each i In FindRange
If i = "criteria" Then
MsgBox "Found" & " " & i.Address
End If
Next i
End Sub
I can not get this code to work, i am getting an ambiguos error i do need all 3 message boxes to come up at the same time if the < value is met. Thanks any help is appreciated
Private Sub Worksheet_Calculate()
If Range("F10").Value < 5300 Then
MsgBox "Initial Load Must be at least 5,300Kgs"
End If
End Sub
Private Sub Worksheet_Calculate()
If Range("G10").Value < 4300 Then
MsgBox "Initial Load Must be at least 4,300Kgs"
End If
End Sub
Private Sub Worksheet_Calculate()
If Range("H10").Value < 1300 Then
MsgBox "Initial Load Must be at least 1,300Kgs"
End If
End Sub
Private Sub Worksheet_Calculate()
If Range("F10").Value < 5300 Then
MsgBox "Initial Load Must be at least 5,300Kgs"
End If
End Sub
Private Sub Worksheet_Calculate()
If Range("G10").Value < 4300 Then
MsgBox "Initial Load Must be at least 4,300Kgs"
End If
End Sub
Private Sub Worksheet_Calculate()
If Range("H10").Value < 1300 Then
MsgBox "Initial Load Must be at least 1,300Kgs"
End If
End Sub
rizvisa1
4256Posts
Thursday January 28, 2010Registration date
ContributorStatus
June 18, 2013Last seen

Mar 13, 2010 4:45pm GMT
Not know exactly why message boxes are needed. But in case you can live with one message box that have all possible errors, you can try this
Private Sub Worksheet_Calculate() Dim myMessage As String myMessage = "" If Range("F10").Value < 5300 Then If (myMessage <> "") Then myMessage = myMessage & vbCrLf myMessage = myMessage & "Initial Load Must be at least 5,300Kgs" End If If Range("G10").Value < 4300 Then If (myMessage <> "") Then myMessage = myMessage & vbCrLf myMessage = myMessage & "Initial Load Must be at least 4,300Kgs" End If If Range("H10").Value < 1300 Then If (myMessage <> "") Then myMessage = myMessage & vbCrLf myMessage = myMessage & "Initial Load Must be at least 1,300Kgs" End If If (myMessage <> "") Then MsgBox (myMessage) End Sub
cx72go

Mar 13, 2010 11:21pm GMT
I am trying to tell a lab person when there is not enough product in a vessel to hit the agitator blades at the same time i am trying to remind him how much he does need. i first tried to make an adjacent cell highlight and appear with the correct value using conditional formatting but i was unable to get that to work so i tried vba.
Thanks jerry
Thanks jerry
rizvisa1
4256Posts
Thursday January 28, 2010Registration date
ContributorStatus
June 18, 2013Last seen

Mar 14, 2010 12:19am GMT
Jerry I meant there are three messages. So potentially three time ok needs to be clicked. There can also be one message with all the warnings listed. Thats what I tried to show in the macro in message #5. I thought it may be of interest to you. Of course there might be cases, where every warning needs to come up one by one and this might be one of those case.
cx72go

Mar 14, 2010 1:51am GMT
i dd like the one message box better i wish i had your talent at this stuff, i have two sections that need these reminder messages on the same page, is there a way to kkeep them seperate? this is what i have now:
Private Sub Worksheet_Calculate()
Dim myMessage As String
myMessage = ""
If Range("F10").Value < 5300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 5,300Kgs for R5"
End If
If Range("G10").Value < 4300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 4,300Kgs for R4"
End If
If Range("H10").Value < 1300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,300Kgs for R3 "
End If
If Range("H10").Value < 3000 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 3,000Kgs for R2 "
End If
If Range("F22,G22").Value < 1450 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,450Kgs for #4 & 5 Premix"
End If
If Range("H22").Value < 1675 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,675Kgs for #3 Premix"
End If
If Range("I22").Value < 905 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 905Kgs for #2 Premix"
End If
If (myMessage <> "") Then MsgBox (myMessage)
End Sub
Private Sub Worksheet_Calculate()
Dim myMessage As String
myMessage = ""
If Range("F10").Value < 5300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 5,300Kgs for R5"
End If
If Range("G10").Value < 4300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 4,300Kgs for R4"
End If
If Range("H10").Value < 1300 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,300Kgs for R3 "
End If
If Range("H10").Value < 3000 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 3,000Kgs for R2 "
End If
If Range("F22,G22").Value < 1450 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,450Kgs for #4 & 5 Premix"
End If
If Range("H22").Value < 1675 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 1,675Kgs for #3 Premix"
End If
If Range("I22").Value < 905 Then
If (myMessage <> "") Then myMessage = myMessage & vbCrLf
myMessage = myMessage & "Initial Load Must be at least 905Kgs for #2 Premix"
End If
If (myMessage <> "") Then MsgBox (myMessage)
End Sub
rizvisa1
4256Posts
Thursday January 28, 2010Registration date
ContributorStatus
June 18, 2013Last seen

Mar 14, 2010 3:13am GMT
First, I am not sure if this is correct
If Range("F22,G22").Value < 1450 Then
you can have it like this
If ( (Range("F22") < 1450) AND (Range("G22") < 1450) ) Then
or
If ( (Range("F22") < 1450) OR (Range("G22") < 1450) ) Then
depending what you want.
From what I understood, you want two set of possible warnings, with in this one routine. You can do that
so after first msgbox pops up ( If (myMessage <> "") Then MsgBox (myMessage) )
after that line
write
myMessage =""
This will reset the message to a blank string. And then you can again start adding the if statements as before and then finally again the code for msgbox to pop up, once done with "IFs"
If Range("F22,G22").Value < 1450 Then
you can have it like this
If ( (Range("F22") < 1450) AND (Range("G22") < 1450) ) Then
or
If ( (Range("F22") < 1450) OR (Range("G22") < 1450) ) Then
depending what you want.
From what I understood, you want two set of possible warnings, with in this one routine. You can do that
so after first msgbox pops up ( If (myMessage <> "") Then MsgBox (myMessage) )
after that line
write
myMessage =""
This will reset the message to a blank string. And then you can again start adding the if statements as before and then finally again the code for msgbox to pop up, once done with "IFs"
Not a member yet?
signup, it takes less than a minute and it's free!
Members get more answers than anonymous users.
Being a member gives you detailed monitoring of your requests.
Being a member gives you additional options.