VBA Worksheet_Calculate() Code Results in Popup Message Showing Twice

hhoang3

New Member
Joined
Sep 15, 2018
Messages
2
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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