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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,792
Office Version
  1. 365
Platform
  1. Windows
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,450
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,111
Messages
5,835,450
Members
430,357
Latest member
Greshapa

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
Top