JohnGow383
Board Regular
- Joined
- Jul 6, 2021
- Messages
- 141
- Office Version
- 2013
- Platform
- Windows
Hi,
I'm posting this a new thread since it's changed topic. I was kindly given the below code from someone on this forum. It does what it says however, I need contraints added to it so it doesn't fire unless these conditions are satisfied. The current code is as follows:
The code is looking at the calculated values in the trigger cells and displaying a warning message in merged cells "H13:K14" as well as a message box. I would like each trigger cell to be ignored if the following conditions are met.
Trigger Cells:
Cell1 = "H6" If C5 cell is blank to not fire
Cell2 = "H7" If C5 cell is blank to not fire
Cell3 = "E3" If C4 cell is blank to not fire
Cell4 = "I9" If C6 cell is blank to not fire
The calculation is between the above mentioned cells (C4, C5 & C6) and other cells. These are being cleared daily and new figures added. It's only when these cells are then populated that I want it to check that a wrong number has been entered and if so it triggers the warning message. With the cells blank it's triggering before any numbers are added.
Thanks
I'm posting this a new thread since it's changed topic. I was kindly given the below code from someone on this forum. It does what it says however, I need contraints added to it so it doesn't fire unless these conditions are satisfied. The current code is as follows:
VBA 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
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
The code is looking at the calculated values in the trigger cells and displaying a warning message in merged cells "H13:K14" as well as a message box. I would like each trigger cell to be ignored if the following conditions are met.
Trigger Cells:
Cell1 = "H6" If C5 cell is blank to not fire
Cell2 = "H7" If C5 cell is blank to not fire
Cell3 = "E3" If C4 cell is blank to not fire
Cell4 = "I9" If C6 cell is blank to not fire
The calculation is between the above mentioned cells (C4, C5 & C6) and other cells. These are being cleared daily and new figures added. It's only when these cells are then populated that I want it to check that a wrong number has been entered and if so it triggers the warning message. With the cells blank it's triggering before any numbers are added.
Thanks