Need vba to click the OK button in Msgbox

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
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:
Code:
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,312
You don't have to select a sheet to unprotect it.

Code:
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...
Code:
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
 
Last edited:

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
Thanks guys. Used AlphaFrog's solution below. Worked great with the Protect command as well. And, MUCH quicker than what I was using!!

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,156
Members
410,902
Latest member
G Slim
Top