Auto fit columns and rows while in protected mode

[Livin404]

I trying to have my columns and rows adjust automatically while the sheet is protected. There is no password. I got it to work no problem while the worksheet is unprotected. I added Sheets ("Xman") at the begging and end of VBA but it made no difference. I'm hoping it is just a minor correction.

Thank you,

Option Explicit
 
Sub AutoFitAll()
     
    Sheets("XMan").Unprotect
    Application.ScreenUpdating = False
    Dim wkSt As String
    Dim wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkBk.Activate
        Cells.EntireColumn.AutoFit
        Cells.EntireRow.AutoFit
    Next wkBk
    Sheets(wkSt).Select
    Application.ScreenUpdating = True
    Sheets("XMan").Protect
End Sub

 

[Dossfm0q]

Application.ScreenUpdating = True
    Sheets("XMan").Protect, AllowFormattingColumns:=True, AllowFormattingRows:=True

 

[Livin404]

I tried different variations of this from removing what I had and replacing it what you provided, and I replaced a portion of what I had. Am I missing something, because unfortunately nothing changed.?
Thank you,

 

[Michael M]

Try

Sub AutoFitAll()
    Application.ScreenUpdating = False
    Dim wkSt As String, wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In Worksheets
        wkBk.Activate
        wkBk.Unprotect
        Cells.EntireColumn.AutoFit
        Cells.EntireRow.AutoFit
        wkBk.Protect
    Next wkBk
    Sheets(wkSt).Select
    Application.ScreenUpdating = True
End Sub

 

[Livin404]

I tried yours, and for now it wants to autofill on other worksheets, I only need this specifically on one worksheet. Though this VBA is placed in the worksheet and not in a module I don't think that should affect the coding, Yet I can't be sure as I'm still new to this.

 

[Livin404]

Hello,
I found something much simpler and I still locked the worksheet I just checked the format rows and columns. The users still cannot mess with the settings.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
Rows.AutoFit
End Sub

 

[Livin404]

Hello,
I found something much simpler and I still locked the worksheet I just checked the format rows and columns. The users still cannot mess with the settings.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
Rows.AutoFit
End Sub

 

[Michael M]

Well, your initial post suggested ALL sheets in the workbook !!

For Each wkBk In Worksheets

Hence my solution

 

[Livin404]

Well, your initial post suggested ALL sheets in the workbook !!

For Each wkBk In Worksheets

Hence my solution

You’re right, this is a slow process for me in this learning phase. Thanks all the same, it did me look a little harder. Thank you