Auto fit columns and rows while in protected mode

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,
VBA Code:
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Dossfm0q

Well-known Member
Joined
Mar 9, 2009
Messages
504
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Application.ScreenUpdating = True
    Sheets("XMan").Protect, AllowFormattingColumns:=True, AllowFormattingRows:=True
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,504
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
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

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
Rows.AutoFit
End Sub
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
Rows.AutoFit
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,504
Office Version
  1. 2013
Platform
  1. Windows
Well, your initial post suggested ALL sheets in the workbook !!
VBA Code:
For Each wkBk In Worksheets
Hence my solution
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Well, your initial post suggested ALL sheets in the workbook !!
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,062
Messages
5,575,878
Members
412,689
Latest member
nhsmedic
Top