Formatting columns in a protected sheet

[afrattar]

Hello,

I have a spreadsheet that I would like to protect but allow for column formatting. I have a macro in the sheet to allow for more than one dropdown choice. This unprotects and protects and I think may be the problem. I have tried adding the following with no success. As soon as I add data to a cell the ability to format the column is not available.

Sub ProtectButAllowFormatting()

ActiveSheet.Protect _
Password:=(123), _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True

End Sub


Here is the vba for the dropdown. Can you help me with adding the formatting for columns to work in a protected sheet with this also running?

Private Sub Worksheet_Change(ByVal Target As Range)
' Code by Sumit Bansal from TrumpExcel
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Me.Unprotect Password:="123"
On Error GoTo ExitSub
If (Target.Column = 18 Or Target.Column = 22) Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo ExitSub
ElseIf Target.Value = "" Then
GoTo ExitSub
Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else
Target.Value = Oldvalue
End If
End If
End If
ExitSub:
Application.EnableEvents = True
Me.Protect Password:="123"
End Sub

 

[afrattar]

For anyone who wants to know.
I changes the 'Me.Protect Password:="123" just before End Sub
to
Me.Protect Password:="123", AllowFormattingColumns:=True, AllowFormattingRows:=True