Need vba to click the OK button in Msgbox

samsilverman

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,275
Messages
6,118,623
Members
448,844
Latest member
Jayee04e

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