Need vba to click the OK button in Msgbox

[samsilverman]

I have a message box that pops up with a warning message every time a user activates a worksheet (PATIENT). It works great.

I also have a button on the main worksheet (ACCOUNT) that allows me to unprotect or protect every worksheet when the button is clicked. It also works great.

Of course now when I click on the Unprotect/Protect button, the message box pops up when it gets to the PATIENT worksheet. That's kind of annoying. Is there a way to have the protect/unprotect codes virtually hit the "OK" button on the msgbox instead of having to do it manually each time?

The unprotect coding is:

Private Sub CommandButton2_Click()
Sheets("AREAS").Select
    ActiveSheet.Unprotect Password:="areas"
Sheets("INVOICE").Select
    ActiveSheet.Unprotect Password:="invoice"
Sheets("VENTILATION").Select
    ActiveSheet.Unprotect Password:="ventilation"
Sheets("CHEMICAL").Select
    ActiveSheet.Unprotect Password:="chemical"
Sheets("PATIENT").Select
    ActiveSheet.Unprotect Password:="patient"
Sheets("MEDGAS").Select
    ActiveSheet.Unprotect Password:="medgas"
Sheets("ACCOUNT").Select
    ActiveSheet.Unprotect Password:="account"
Range("A1").Select
End Sub

Thanks for any help! I'm assuming any add-on coding for the unprotect coding would work in the protect coding as well as they are pretty much the same.

 

[Ruddles]

At the start of CommandButton2_Click code, try issuing a Application.DisplayAlerts = False and then back to Application.DisplayAlerts = True at the end.

Failing that, set a flag at the start of CommandButton2_Click which indicates that the activate code is not to display the message, then in the activate code, check the flag to determine whether the message box is required.

 

[AlphaFrog]

You don't have to select a sheet to unprotect it.

Private Sub CommandButton2_Click()
   Sheets("AREAS").Unprotect Password:="areas"
   Sheets("INVOICE").Unprotect Password:="invoice"
   Sheets("VENTILATION").Unprotect Password:="ventilation"
   Sheets("CHEMICAL").Unprotect Password:="chemical"
   Sheets("PATIENT").Unprotect Password:="patient"
   Sheets("MEDGAS").Unprotect Password:="medgas"
   Sheets("ACCOUNT").Unprotect Password:="account"
End Sub

Better yet...

Private Sub CommandButton2_Click()

    Dim ws As Worksheet
    
    For Each ws In Worksheets(Array("AREAS", "INVOICE", "VENTILATION", _
                             "CHEMICAL", "PATIENT", "MEDGAS", "ACCOUNT"))
        ws.Unprotect Password:=LCase(ws.Name)
   Next ws
   
End Sub

 

[samsilverman]

Thanks guys. Used AlphaFrog's solution below. Worked great with the Protect command as well. And, MUCH quicker than what I was using!!

Private Sub CommandButton2_Click()

    Dim ws As Worksheet
    
    For Each ws In Worksheets(Array("AREAS", "INVOICE", "VENTILATION", _
                             "CHEMICAL", "PATIENT", "MEDGAS", "ACCOUNT"))
        ws.Unprotect Password:=LCase(ws.Name)
   Next ws
   
End Sub