Problems with Protecting and Unprotecting Sheet with VBA

JohnGow383

Board Regular
Joined
Jul 6, 2021
Messages
133
Office Version
  1. 2013
Platform
  1. Windows
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")

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

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

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

rasinc

Board Regular
Joined
Jun 28, 2011
Messages
113
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

Board Regular
Joined
Jul 6, 2021
Messages
133
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jun 28, 2011
Messages
113
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

Board Regular
Joined
Jul 6, 2021
Messages
133
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,583
Messages
5,898,302
Members
434,702
Latest member
jhum

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