Issue with protect vba

[pujo]

I have this vba, it will unprotect, and it all works except at the end, it will not protect. Here is the code, I hope someone could assist me with this.

Private Sub CommandButton1_Click()


      For i = 1 To Sheets.Count
         Sheets(i).Unprotect Password:=""
      Next i

With Application
        .ScreenUpdating = False
        .EnableEvents = False

        Range("J7") = Now()
        
If Sheets("Wednesday").Range("E7").Value = "Wednesday" Then

    Sheets(Array("Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", _
        "Tuesday")).Select
    Sheets("Wednesday").Activate
    Range("B9:G25").Select
    Selection.ClearContents
    Range("B28:I37").Select
    Selection.ClearContents
    Range("B40:B47").Select
    Selection.ClearContents
    Range("B9").Select
    Sheets("Wednesday").Select
    Else
    MsgBox "Today is not Wednesday. You can only create a new report on Wednesday !", vbOKOnly, "Hey !"
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    Range("B9").Select

      For i = 1 To Sheets.Count
         Sheets(i).Protect Password:=""
      Next i
         End With
      End If
  End With

 
End Sub

Thanks,
Pujo

 

[diddi]

see if this helps

      For i = 1 To Sheets.Count
         Sheets(i).Protect 
      Next i

 

[pujo]

That is same thing I have except for

Password:=""

But I tried it like you suggested

For i = 1 To Sheets.Count
         Sheets(i).Protect
      Next i

Still not working, Also here is the original code.

For i = 1 To Sheets.Count
         Sheets(i).Protect Password:=""
      Next i

Thanks for the quick reply!
Any other ideas?

 

[AlphaFrog]

In your code you unprotect the sheets. Then you test if it is Wednesday. If it is not Wednesday, the code between Else and End If is run which includes the For\Next loop to protect the sheets. If it is Wednesday, the code to protect the sheets is not run because it's in the Else block of the IF statement.

You should move the For\Next loop to protect the sheets after the End If block statement.

 

[ZVI]

Unprotection of sheets is not required at all

On top of code replace this:

  For i = 1 To Sheets.Count
    Sheets(i).Unprotect Password:=""
  Next i

by that:

  For i = 1 To Sheets.Count
    Sheets(i).Protect UserInterfaceOnly:=True
  Next

And remove this part at the bottom of code:

  For i = 1 To Sheets.Count
    Sheets(i).Protect Password:=""
  Next i

 

[ZVI]

The possible code:

Private Sub CommandButton1_Click()
  
  Dim i As Long, ShName

  On Error GoTo if_err
  For i = 1 To Sheets.Count
    Sheets(i).Protect UserInterfaceOnly:=True
  Next
  
  ' Check Wednesday
  Sheets("Wednesday").Select
  Range("J7") = Now()
  If Sheets("Wednesday").Range("E7").Value <> "Wednesday" Then
    Range("E7").Select
    MsgBox "Today is not Wednesday. You can only create a new report on Wednesday !", vbOKOnly + vbExclamation, "Hey !"
    Exit Sub
  End If
  
  ' Clean up sheets
  Application.EnableEvents = False
  For Each ShName In Split("Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday", ",")
    Sheets(ShName).Range("B9:G25,B28:I37,B40:B47").ClearContents
  Next
  Application.EnableEvents = True

if_err:
  If Err Then MsgBox Err.Description, vbCritical, "Error"

End Sub

 

[pujo]

I really like the way you cleaned up the logic. I do keep getting an error.
"Cannot change part of a merged cell" any idea what could be causing this error?

The "UserInyerfaceOnly"statement is new to me, can you explain how this works?

Thanks everyone or the info.

 

[ZVI]

I really like the way you cleaned up the logic. I do keep getting an error.
"Cannot change part of a merged cell" any idea what could be causing this error?

You can use your method or add more parts to ranges addresses with including of all cells for the merged ranges.

The "UserInterfaceOnly" statement is new to me, can you explain how this works?

UserInterfaceOnly named parameter means that protection is applied only to the user interface, that is - to the manual working with Excel. But you can modify protected sheets via VBA in this case. Take into account that UserInterfaceOnly parameter is not saved with workbook and repeating of the protection code with that parameter is required after opening of workbook.

 

[pujo]

That's good info, I will use this more often.
I did get my merged cell issue fixed, the logic works like it is suppose to.
Have a great evening!
Pujo

 

[ZVI]

Yea, it’s a good place to find something new daily
Have a nice day, Pujo!
Vlad