VBA Code to Enter a String Text when the trigger cell's value meets certain conditions

[JohnGow383]

Hi all,

I would like code to automatically generate text in a cell when another cell's value meets the preset conditions. I may also want to add a message box too.
So for example:
Trigger Cell = H6 if Value of H6<0 or H6>200 to populate merged cells "H13:K14" with text "Warning! Please Check the LSFO Meter Readings"
To also add a message box to the same effect (I can remove later if this is too annoying).
Once the error condition is no longer met, i.e. the value of H6 is between 0 to 200, for the populated merged cell "H13:K14" to have it's contents cleared.

I also have other trigger cells, where the warning message would be slightly different, but would it be just a case of repeating a small section of the code within the same macro or would I have to make multiple macros for each trigger cell?

Thanks

 

[JohnGow383]

Thanks.

Opp, sorry, I forgot to tell you that calculate even does not require target. Try to remove 2 lines of "If ...End if"

This works!
So to add more trigger cells I can follow instructions as above. Thanks

Private Sub Worksheet_Calculate()
Dim ce1 As Range, msg1 As String
Set ce1 = Range("H6"): msg1 = "Warning! Please Check the LSFO Meter Readings"
    With Range("H13:K14")
                    Select Case ce1.Value
                Case 0 To 200
                    .ClearContents
                Case Else
                    .Value = msg1
                    MsgBox msg1
            End Select
            End With
End Sub

 

[JohnGow383]

Opp, sorry, I forgot to tell you that calculate even does not require target. Try to remove 2 lines of "If ...End if"

Hi again,

I'm sorry, I have unforseen problem now which I didn't initally take into consideration. I have macro button which clears yesterday's data but copies and pastes it into yesterday' column in preparation for entering today's data manually. The formula then calcualte the consumptions and that's where I wanted your macro to trigger if a wrong counter reading was entered. The problem with the macro now is, it is triggering when the data is cleared and before entering the new numbers. Here is my code so far:

Private Sub Worksheet_Calculate()

Dim Cell1 As Range, msg1 As String
Dim Cell2 As Range, msg2 As String
Dim Cell3 As Range, msg3 As String
Dim Cell4 As Range, msg4 As String

Set Cell1 = Range("H6"): msg1 = "WARNING!!! Please Check the LSFO Meter Reading!"
Set Cell2 = Range("H7"): msg2 = "WARNING!!! Please Check the LSMGO Meter Reading!"
Set Cell3 = Range("E3"): msg3 = "WARNING!!! Please Check the M/T Counter Reading!"
Set Cell4 = Range("I9"): msg4 = "WARNING!!! Please Check the Gas Counter Reading!"

With Range("H13:K14")
Select Case Cell1.Value
Case 0 To 200
.ClearContents
Case Else
.Value = msg1
MsgBox msg1
End Select
End With

With Range("H13:K14")
Select Case Cell2.Value
Case 0 To 200
.ClearContents
Case Else
.Value = msg2
MsgBox msg2
End Select
End With

With Range("H13:K14")
Select Case Cell3.Value
Case 0 To 82
.ClearContents
Case Else
.Value = msg3
MsgBox msg3
End Select
End With

With Range("H13:K14")
Select Case Cell4.Value
Case 0 To 400
.ClearContents
Case Else
.Value = msg4
MsgBox msg4
End Select
End With

End Sub

So I need to add restrictions to ignore doing this when the corresponding data cells are blank. Looking at the first example, for msg1, I would want it to not run if cell C5=""
For condition 2 to ignore if C5 is also blank
For condition 3 to ignore if C4 is blank
For condition 4 to ignore if C6 is blank

Are you able to help me with this please? I'm sorry I did not stipulate this at the beginning I just didn't realize until fully testing it.

 

[bebo021999]

Assum C4 and C5 and C6 is not blank, AND, cell1 =201, cell2=201,cell3=83, cell4=401
all conditions are to establish warning message in H13:K14
what msg should display in H13:K14? msg1 or 2 or 3 or 4?
In your code, it is last msg4

 

[JohnGow383]

Assum C4 and C5 and C6 is not blank, AND, cell1 =201, cell2=201,cell3=83, cell4=401
all conditions are to establish warning message in H13:K14
what msg should display in H13:K14? msg1 or 2 or 3 or 4?
In your code, it is last msg4

Hi, I managed to get a solution by adding the constraints into your code. I have more triggers now. Here is the code

Private Sub Worksheet_Calculate()

Dim Cell1 As Range, msg1 As String
Dim Cell2 As Range, msg2 As String
Dim Cell3 As Range, msg3 As String
Dim Cell4 As Range, msg4 As String
Dim Cell5 As Range, msg5 As String
Dim Cell6 As Range, msg6 As String
Dim Cell7 As Range, msg7 As String
Dim Cell8 As Range, msg8 As String
Dim Cell9 As Range, msg9 As String

Set Cell1 = Range("H6"): msg1 = "WARNING!!! Please Check the LSFO Meter Reading!"
Set Cell2 = Range("H7"): msg2 = "WARNING!!! Please Check the LSMGO Meter Reading!"
Set Cell3 = Range("E3"): msg3 = "WARNING!!! Please Check the M/T Counter Reading!"
Set Cell4 = Range("I9"): msg4 = "WARNING!!! Please Check the Gas Counter Reading!"
Set Cell5 = Range("B9"): msg5 = "WARNING!!! Please Check No 1 Nitrogen Counter!"
Set Cell6 = Range("B10"): msg6 = "WARNING!!! Please Check No 2 Nitrogen Counter!"
Set Cell7 = Range("E9"): msg7 = "WARNING!!! Please Check D/Alt Counters!"
Set Cell8 = Range("Z3"): msg8 = "High distilled water consumption is because ..."
Set Cell9 = Range("Z4"): msg9 = "High domestic water consumption is because ..."

With Range("H13:K14")
    If Not IsEmpty(Range("C5")) And Range("J17") = "LSFO" Then
        Select Case Cell1.Value
        Case 0 To 200
        .ClearContents
        Case Else
        .Value = msg1
        MsgBox msg1, vbExclamation, Title:="LSFO Counter Error"
        TalkFOCtr
        End Select
    End If
End With

With Range("H13:K14")
    If Not IsEmpty(Range("C5")) And Range("J17") = "LSMGO" Then
        Select Case Cell2.Value
        Case 0 To 200
        .ClearContents
        Case Else
        .Value = msg2
        MsgBox msg2, vbExclamation, Title:="LSMGO Counter Error"
        TalkFOCtr
        End Select
    End If
End With

'M/T counter
With Range("H13:K14")
    If Not IsEmpty(Range("C4")) And Not Range("E3") = "N/A" Then
        Select Case Cell3.Value
        Case 0 To 82
        .ClearContents
        Case Else
        .Value = msg3
        MsgBox msg3, vbExclamation, Title:="M/T Counter Error"
        Call TalkMTCtr
        End Select
    End If
End With

'Gas Counter
With Range("H13:K14")
    If Not IsEmpty(Range("C6")) Then
        Select Case Cell4.Value
        Case 0 To 400
        .ClearContents
        Case Else
        .Value = msg4
        MsgBox msg4, vbExclamation, Title:="Gas Counter Error"
        Call TalkGasCtr
        End Select
    End If
End With

'No 1 N2 Hours
With Range("H13:K14")
    If Not IsEmpty(Range("C7")) Then
        Select Case Cell5.Value
        Case 0 To 24
        .ClearContents
        Case Else
        .Value = msg5
        MsgBox msg5, vbExclamation, Title:="No 1 N2 Plant Counter Error"
        TalkN2Ctr1
        End Select
    End If
End With

'No 2 N2 Hours
With Range("H13:K14")
    If Not IsEmpty(Range("C8")) Then
        Select Case Cell6.Value
        Case 0 To 24
        .ClearContents
        Case Else
        .Value = msg6
        MsgBox msg6, vbExclamation, Title:="No 2 N2 Plant Counter Error"
        TalkN2Ctr2
        End Select
    End If
End With

'D/Alt Counters
With Range("H13:K14")
    If Not IsEmpty(Range("C11")) And Not IsEmpty(Range("C12")) Then
        Select Case Cell7.Value
        Case 0 To 20
        .ClearContents
        Case Else
        .Value = msg7
        MsgBox msg7, vbExclamation, Title:="D/Alt Counter Error"
        Call TalkDAltCtr
        End Select
    End If
End With

'Distilled Water Consumption
With Range("H13:K14")
    If Not IsEmpty(Range("I3")) Then
        Select Case Cell8.Value
        Case 0 To 20
        .ClearContents
        Case Else
        .Value = msg8
        MsgBox "WARNING!!! High Distilled Water Consumption! - Above 20tons. Complete the Sentance in the Below Message Box Giving a Valid Reason", vbExclamation, Title:="High Distilled Water Consumption"
        Call TalkDistilled
        End Select
    End If
End With

'Domestic Water Consumption
With Range("H13:K14")
    If Not IsEmpty(Range("I4")) Then
        Select Case Cell9.Value
        Case 0 To 20
        .ClearContents
        Case Else
        .Value = msg9
        MsgBox "WARNING!!! High Domestic Water Consumption! - Above 20tons. Complete the Sentance in the Below Message Box Giving a Valid Reason", vbExclamation, Title:="High Distilled Water Consumption"
        Call TalkDomestic
        End Select
    End If
End With

End Sub

What's really weird is for some of them the populated message in merged cell ("H13:K14") populates but then immediatly dissapears even though the condition for firing remains, and then for others it correctly stays. Not sure why this would happen?

 

[bebo021999]

What's really weird is for some of them the populated message in merged cell ("H13:K14") populates but then immediatly dissapears

That what I mentioned in #23.
There are 9 conditions to populate text in H3:K14. What if multiply conditions match?
For example,
Condition 8 match, then H3:K14 read "WARNING!!! High Distilled Water Consumption! - Above 20tons. Complete the Sentance in the Below Message Box Giving a Valid Reason"
Next line, condition 9 does not match, then H3:K14 clear content.

So, the screen display text, then blank again.

 

[JohnGow383]

An example of the above is for Distilled & Domestic water consumption. The code populates a message into merged cell ("H13:K14"), this is also used for general comments. The idea being it will prompt them to give me an explanation as to why there is high water consumption. For domestic water the statement "High domestic water consumption is because ..." remains but for distilled it dissapears in a split second. It does this with one or two of the counter ones too. It's not really a big deal but just curious why this would happen when the code for each block is the same. Unless it's looking at the next thing and clearing the cell but just not sure why

 

[JohnGow383]

Sorry I repleid before I saw your reply. Yeah I see what's happening now. It's checking in the order. Before I added the code to not do it if the calcualtion input cells were blank, it went crazy and got itself into a cycle as every condition was being fired together. It's working pretty well now and I'm not sure if that can be fixed. It would generally happen as they were putting the numbers in, they'd then fix the error and move onto the next input. The only one that is a little bit annoying is the water one as that isn't really an error as such, it could be because of problem on the ship and not a typo

That what I mentioned in #23.
There are 9 conditions to populate text in H3:K14. What if multiply conditions match?
For example,
Condition 8 match, then H3:K14 read "WARNING!!! High Distilled Water Consumption! - Above 20tons. Complete the Sentance in the Below Message Box Giving a Valid Reason"
Next line, condition 9 does not match, then H3:K14 clear content.

So, the screen display text, then blank again.