Dialogue Box which is displayed if cell value exceeds X - how to only appear once

[scotthannaford1973]

Hi

I have a cell (D45) where if the value exceeds 12, a dialogue box appears when worksheet_deactivate. Works fine, but if the cell is not amended then the message box comes up every subsequent time there is worksheet_deactivate. Can I specify that the box appears a maximum number of times (twice) if the cell value is not changed and the warning is, therefore, ignored?

cheers!


Private Sub Worksheet_Deactivate()


If Range("D45").Value > "12" Then
MsgBox "You have a risk/issue (cell A45 on the QIA Worksheet) with a total score of 16 or greater - please consider escalating it to the Corporate Risk Register"
End If
End Sub

 

[tyija1995]

You could try add a static variable to the code

Private Sub Worksheet_Deactivate()
    Static count As Integer


    If Range("D45").Value > 12 And count < 2 Then
        MsgBox "You have a risk/issue (cell A45 on the QIA Worksheet) with a total score of 16 or greater - please consider escalating it to the Corporate Risk Register"
        count = count + 1
    End If
End Sub

The static variable will increment every time this code runs. It will reset upon closing the workbook or by manual reset inside the VBE.

 

[Fluff]

How is D45 being changed?

 

[scotthannaford1973]

How is D45 being changed?

Basically B45 and C45 each have drop downs and when the user selects them, D45 calculates the risk rating (effectively just a fancy D45 = B45 * C45)

 

[scotthannaford1973]

You could try add a static variable to the code

Private Sub Worksheet_Deactivate()
    Static count As Integer


    If Range("D45").Value > 12 And count < 2 Then
        MsgBox "You have a risk/issue (cell A45 on the QIA Worksheet) with a total score of 16 or greater - please consider escalating it to the Corporate Risk Register"
        count = count + 1
    End If
End Sub

The static variable will increment every time this code runs. It will reset upon closing the workbook or by manual reset inside the VBE.

thanks - will give that a go and feed back.

 

[Fluff]

Another option, which will take into account D45 being changed

[COLOR=#ff0000]Dim Chk As Long[/COLOR]
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Mcl As Range
   Set Mcl = Range("D45")
   On Error Resume Next
   Set Mcl = Range("D45").Precedents
   On Error GoTo 0
   If Not Intersect(Mcl, Target) Is Nothing Then
     Chk = 0
   End If
End Sub
Private Sub Worksheet_Deactivate()
   If Range("D45").Value > 12 And Chk < 2 Then
       MsgBox "You have a risk/issue (cell A45 on the QIA Worksheet) with a total score of 16 or greater - please consider escalating it to the Corporate Risk Register"
   End If
   Chk = Chk + 1
End Sub

The line in red must go at the very top of the module, before any coe.