how to add counter in msgbox to show remain times the message will show vba code

[sspreyer]

hi i have some code that check column c if value 133 enter msgbox pops up but
once there is 10 cells fill with 133 the msgbox will stop but can also offset this by
the values in column f eg if column c has 133 enter 6 times and if a cell in
column f has 4 in it this will stop message from pop up what i'm trying to do is
show in msgbox remain times the message will appear also would like this to to work on the same with values 111 122, here my code

thanks in advance

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngShow As Long
Dim lngCounter As Long
Dim lngLastRow As Long
If Target.Column <> 3 Then Exit Sub
If Target.Count > 1 Then Exit Sub
lngLastRow = Range("C" & Rows.Count).End(xlUp).Row
Select Case Target.Value
  Case 133
  ' Offset
    lngCounter = 10
    If WorksheetFunction.CountIf(Columns(3), Target.Value) > lngCounter Then Exit Sub
    lngShow = lngCounter - Evaluate("=SUMPRODUCT(((C1:C" & lngLastRow & ")=133)*(F1:F" & lngLastRow & "))") - WorksheetFunction.CountIf(Columns(3), Target.Value)
  Case 122
    lngCounter = 6
    If WorksheetFunction.CountIf(Columns(3), Target.Value) > lngCounter Then Exit Sub
    lngShow = lngCounter - Evaluate("=SUMPRODUCT(((C1:C" & lngLastRow & ")=122)*(F1:F" & lngLastRow & "))") - WorksheetFunction.CountIf(Columns(3), Target.Value)
  Case 111
    lngCounter = 2
    If WorksheetFunction.CountIf(Columns(3), Target.Value) > lngCounter Then Exit Sub
    lngShow = lngCounter - Evaluate("=SUMPRODUCT(((C1:C" & lngLastRow & ")=111)*(F1:F" & lngLastRow & "))") - WorksheetFunction.CountIf(Columns(3), Target.Value)
  Case Else
    Exit Sub
End Select
If lngShow > -1 Then
 
  MsgBox "My Record Are Showing There Is Stock In The Asset Crib" & Chr(13) & Chr(13)
    Application.EnableEvents = False 'So that changing cell values here doesn't trigger this macro again
    ActiveSheet.Cells(Target.Row, "e").Value = "asset"
    ActiveSheet.Cells(Target.Row, "g").Value = "asset"
    Application.EnableEvents = True 'so that this macro can again be triggered for future cell value changes
End If
    
    
End Sub

shane

 

[VoG]

Cross posted how to add counter in msgbox to show remain times the message will show vba code