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

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
115
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You could try add a static variable to the code

Rich (BB 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.
 
Upvote 0
You could try add a static variable to the code

Rich (BB 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.
 
Upvote 0
Another option, which will take into account D45 being changed
Code:
[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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top