How to allow "column width change only" in a protected sheet? (As bonus, perhaps also auto-resize column width up to a max width?) VBA?

[Rnkhch]

Hello,

In one of my protected sheets (only two options checked in protect dialog: "select locked cells" and "select unlocked cells"), I want to allow the users to only be able to change the width of columns F and H with no other additional capabilities allowed.

I tried checking the option for "format columns" as well when protecting the sheets, but this also allows the users to hide/unhide and perhaps allows doing other things as well that I don't know. So I'm trying to avoid this scenario.

So I was wondering if there is a way that in my protected sheets (with only the two initial options selected), is there a way to allow only column change (for F and H) and nothing else additional? I'm guessing this would require VBA code?

Also as an added bonus, would it be possible to allow automatic resizing of the width of columns F and H up to a maximum width of say 60? If possible, I was thinking to implement a user-editable dropdown switch (allow automatic column width? yes/no), and then if the user selects 'yes', the VBA code would switch to the automatic adjusting of the width based on the length of the longest cell in the column (up to 60). And if the user selects 'no', then they will be allowed to manually change the column width.

Thanks for any input!


P.S.
(Also please keep in mind that there are other VBA code in my fancy application , and I would need this new code to coexist with the previous codes which can be found in the following posts:

Is it possible to implement an export/import feature for the unlocked cells in my Excel file? (Through VBA?)

Hello, I have made an Excel application, with protected worksheets and workbook, that I have updated and improved over the last several months, and it continues to evolve as I add more features and code as the need arises. One issue though is that each time I make an updated version, I have to...

www.mrexcel.com

How to clear the clipboard when certain cells are selected? (VBA)

Hello, I have some cells that I would prefer my students to type into them rather than just quickly copy/paste. I was wondering if a code could be written for this purpose? In particular, I want to apply this to the range M4:O4 of my sheet to force my students to think and manually type in the...

www.mrexcel.com

How to temporarily unprotect a sheet to allow VBA code to run and reprotect immediately, using VBA?

Hello, First a little background about this question. Actually this is related to and in continuation of another thread of mine (Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?). So in this thread, johnnyL wrote magical code...

www.mrexcel.com

)

 

[Jeffrey Mahoney]

So, here's my input. There is no way to trap events for hiding or resizing a column and having vba from preventing that. You can trap events if a user selects an entire column, therefore you could prevent them from hiding the column. You could resize columns after any of these events: cell selection change, Cell contents change, Sheet selection change, and Table Update.

 

[Jeffrey Mahoney]

I created a named range called AutoWidth and added a data validation with Yes,No. Add the code below to the SHEET level module in VBA

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Target, Union(Range("F:F"), Range("H:H")))
  If Not i Is Nothing Then
    If Range("Autowidth").Value = "Yes" Then
      Target.EntireColumn.AutoFit
      If Target.ColumnWidth > 60 Then Target.ColumnWidth = 60
    End If
  End If
  
  
End Sub

 

[Jeffrey Mahoney]

Altering to include unprotecting the sheet when protected with a password

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Target, Union(Range("F:F"), Range("H:H")))
  If Not i Is Nothing Then
    If Range("Autowidth").Value = "Yes" Then
      ActiveSheet.Unprotect Password:="Jeff"
      Target.EntireColumn.AutoFit
      If Target.ColumnWidth > 60 Then Target.ColumnWidth = 60
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Jeff"
    End If
  End If
  
  
End Sub

 

[Rnkhch]

Thank you! I was just trying it. How do I specify which cell the "Autowidth" yes/no is coming from?

 

[Jeffrey Mahoney]

Thank you! I was just trying it. How do I specify which cell the "Autowidth" yes/no is coming frose

 

[Jeffrey Mahoney]

Select a cell. Add the data validation. Add a named range from the formula menu

 

[Rnkhch]

So I tried this over the weekend on my actual file, but couldn't get it to work. I was just trying a very simple version like you did in a new file, but it didn't work either. Likely I'm doing something wrong. Any chance you could share your sample file you made?

Thank you so much!