Problems with Protecting and Unprotecting Sheet with VBA

[JohnGow383]

I have some code which manipulates a text string in "A1" which works fine when the sheet is unprotected. When I protect the sheet using Excel I tickbox Format Cells and Edit Objects and the code still works fine. I need edit objects as I've other code which manipulates comments (notes). This is a long winded process which is easy to forget so I have code to quickly unprotect the sheet and protect using keyboard shortcuts. However, when I use this the formatting of the cells gives me a runtime error and I don't know why. I AllowFormattingCells:=True. Easier to show the code so here goes.

This is the text manipulation code. The first error is occuring on the first formatting part ("Bold Italic")

Sub CopyVoyNo() 'Copies Voyage Number from Userform13 - Does some fancy formatting. Ensure Worksheet is protected using Ctrl + Shift P and not from the button
                 'In order for this macro to work Protected worksheet needs to have enable edit objects and formatting cells
Dim ws As Worksheet
Dim i As Long
Dim x As Long
Dim z As Long
Dim lastchar As String
Set ws = ThisWorkbook.Worksheets("NOON Figs")

Application.ScreenUpdating = False

ws.Range("A1").Value = ws.Range("A33").Value

lastchar = Right(Range("A1"), 1)
i = InStr(Range("A1"), "V")
x = InStr(Range("A1"), ":") + 1
z = Len(Range("A1"))

With Cells(1, 1).Characters(i, 9).Font
 .FontStyle = "Bold Italic"
End With
 
With Cells(1, 1).Characters(x, 8).Font
 .Color = vbRed
 .Size = 18
End With

Select Case lastchar
   Case "L"
With Cells(1, 1).Characters(z, -1).Font
 .FontStyle = "Bold"
 .ColorIndex = 50
 .Size = 19
End With
   Case "B"
With Cells(1, 1).Characters(z, -1).Font
 .FontStyle = "Bold"
 .ColorIndex = 32
 .Size = 19
End With
  End Select

Application.ScreenUpdating = True

End Sub

Here is the code I'm using to protect the worksheets

Sub ProtectSelectedWorksheets() 'Shortcut is Ctr + Shift P (Dont use on LOG entries until I can figure out how to allow formatting)

Dim ws As Worksheet
Dim sheetArray As Variant
Dim myPassword As Variant

'Set the password - Please use 63360
myPassword = Application.InputBox(Prompt:="Enter password", _
    Title:="Password", Type:=2)

'If Cancel is clicked
If myPassword = False Then Exit Sub

'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each worksheet in the active workbook
For Each ws In sheetArray

    On Error Resume Next
                   
     ws.Select   'Select the worksheet
        
    ws.Protect password:=myPassword, DrawingObjects:=True, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingColumns:=False, AllowFormattingRows:=False 'Protect each worksheet but enables edit objects (for Comment generating macros)
  
    On Error GoTo 0
        
Next ws

sheetArray.Select

End Sub

and here is the code to unprotect

Sub UnprotectAllWorksheets() 'Shortcut is Ctr + Shift U

'Create a variable to hold worksheets
Dim ws As Worksheet

'Create a variable to hold the password
Dim myPassword As Variant

'Set the password
myPassword = Application.InputBox(Prompt:="Enter password", _
    Title:="Password", Type:=2)

'The User clicked Cancel
If myPassword = False Then Exit Sub

'Loop through each worksheet in the active workbook
For Each ws In ActiveWindow.SelectedSheets

    'Protect each worksheet
    ws.Unprotect password:=myPassword
        
Next ws

End Sub

Any ideas why it's not allowing formatting of cells? Thanks

 

[rasinc]

I might be missing something but the code in your first code block Sub CopyVoyNo doesn't issue the UnprotectAllWorksheets statement so the sub doesn't run.

 

[JohnGow383]

Yeah that

I might be missing something but the code in your first code block Sub CopyVoyNo doesn't issue the UnprotectAllWorksheets statement so the sub doesn't run.

that code doesn't fire the unprotect. The protect and unprotect subs are simply key board short cuts. The worksheet is always locked using ctrl shift P. But the copyVoyNo wasn't working when the sheet was manually protected using the protect sub.
I haven't changed anything but it seems to be working now. Very strange. I don't think it's possible to unlock a sheet entirely that's pw protected without being prompted for pw and I don't want that. I want to protect the sheet as much as possible but for the macros to work but I have to accept edit objects and format cells has to be enabled. It's a pity this couldn't be specific to cells rather than whole sheet.
Thanks for the reply

 

[rasinc]

Glad it's working now. I created a spreadsheet a couple of years ago that had to unprotect and then reprotect but I can't find it. I had hoped to see if I had done anything with a password but I don't think so. I just can't find it now. I do know that the unprotect and protect commands were done while a larger amount of VBA code was running, not manually before I ran the rest.

 

[JohnGow383]

Glad it's working now. I created a spreadsheet a couple of years ago that had to unprotect and then reprotect but I can't find it. I had hoped to see if I had done anything with a password but I don't think so. I just can't find it now. I do know that the unprotect and protect commands were done while a larger amount of VBA code was running, not manually before I ran the rest.

Thanks. And yeah I've seen that before. However with mine it's a comunual spreadsheet so I don't want the others being able to unprotect it at all. It's quite a project, I've over 200 macros in it already most of it isn't necessarily tbh but it's been fun learning vba, it's strangely addictive.