6 Responses to “Auto resize columns as you type”

1. Cyril Says:
   February 11th, 2012 at 3:10 am
   
   

   Oscar,

   what code would you suggest in order to allow column resizing in a protected worksheet?
   whereas some cells are locked.
   would still the vba encounter a Run-time error '424' if a line was added to "bypass" the protection?
   (un protect, modify,re-protect)

   such as:

   Sheets(“Sheet1″).Unprotect Password:=” whatever ”
   PROCEDURE
   Sheets(“Sheet1).Protect Password:=” whatever “
   with the risk of having the sheet unprotected if the procedure fails...

   or something like
   Private Sub Workbook_Open()
   Sheets (" sheetname ") .protect _
   Password:=" whatever", _
   UserInterfaceOnly:=True
   End Sub

   Thanks.

2. Cyril Says:
   February 11th, 2012 at 3:12 am
   
   

   Oops,

   forgot to add,
   would this be included in the first code or should it be a separate code?

3. Oscar Says:
   February 16th, 2012 at 8:40 am
   
   

   Cyril,

   You can select "Format rows" and "Format columns" when you protect a sheet.

   Now you can use the macro in a protected sheet.

4. Cyril Says:
   February 16th, 2012 at 9:36 am
   
   

   Yes... true.
   although I locked the sheet to limit the width... I have many users and some tend to be "artistic" and revamp the files.

   Thanks for the reminder.

5. Oscar Says:
   February 20th, 2012 at 1:54 pm
   
   

   Cyril,

   Try this code:

   Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Application.ScreenUpdating = False
   Worksheets(Sh.Name).Unprotect Password:="whatever"
   On Error Resume Next
   For Each Value In Target.Columns
       Worksheets(Sh.Name).Columns(Value.Column).AutoFit
   Next Value
   On Error GoTo 0
   Worksheets(Sh.Name).Protect Password:="whatever"
   Application.ScreenUpdating = True
   End Sub
   

6. Cyril Says:
   February 21st, 2012 at 1:32 am
   
   

   Thanks Oscar!

Leave a Reply