VBA Worksheet_Calculate() Code Results in Popup Message Showing Twice

[hhoang3]

Hi,

I am a beginner with VBA codes, and I am working with the script below. Basically I have the D17:D36 range set as "No" responses. If a user selects "Yes" for more than one of the cells, then I want the message box to show up. The message box is working as it shows up, but I have to click "OK" twice to clear the message box. Is there a way to make it pop up only once? Alternatively, is there a way to automatically close the message box after 2 seconds?


Private Sub Worksheet_Calculate()

If Application.WorksheetFunction.CountIf(Range("D17:D36"), "Yes") > 1 Then
MsgBox "Please submit a separate request form for each request.", vbOKOnly, "Multiple Requests"

End If

End Sub



Thanks,

Hau

 

[Peter_SSs]

Welcome to the MrExcel board!

From your written description it sounds like D17:D36 does not contain formulas but is where the users are choosing Yes/No.
If that is the case, it would make more sense to use the Worksheet_Change event instead of Worksheet_Calculate

If I have guessed correctly then remove that other code and replace it with this and see what happens.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D17:D36")) Is Nothing Then
    If Application.WorksheetFunction.CountIf(Range("D17:D36"), "Yes") > 1 Then
      MsgBox "Please submit a separate request form for each request.", vbOKOnly, "Multiple Requests"
    End If
  End If
End Sub

 

[hhoang3]

Peter - Thank you for looking into this. This worked perfectly!

 

[Peter_SSs]

Peter - Thank you for looking into this. This worked perfectly!

You're welcome. Thanks for letting us know.