Locking users to insert or delete columns in a range

[praveenpatel421983]

Hi,

I am new to Vba but this forum has helped me a lot to learn. Now I am stuck and need help from the experts here again.

I am working on a workbook which has data in many columns. This workbook also has macros for some helpful results for users. First 3 rows of this workbook are used for headers and after that all the rows are used by users to fill the relevant data. Number of rows can go to 1000’s of rows. My concern is out of these many columns I want to freeze some of the columns. What I mean is users should not have access to insert or delete the columns (from column A to column W) but they should be able to edit cells in these cells except first 3 rows to add data or modify data and if they want to add columns they can add after column W.

I tried protecting sheet option but it didn’t give right results. Either it is allowing me to insert columns or not at all for whole sheet (I don’t know exactly, I might not have used right option in protected sheet)

I have also tried adding array to the cells along with locked cells option of protected sheet. It worked well but when user tries to add columns, a message “You cannot change part of an array” pops up. I don’t want users to see this message as some of the users are advanced users and I don’t want them to change and play with the column headers or inserting columns as these columns are used in macros for further use.

My question is, can we change this pop up message to something else like “You are not authorized to add columns” or can we add something in macros to get that message and lock the columns to avoid inserting or deleting?

Please help!

Thanks
Praveen

 

[praveenpatel421983]

Just checking back as I didn't get any responses. I am not sure whether I made my question clear or not. Please let me know if it is not clear.

Thanks
Praveen

 

[praveenpatel421983]

Ok. I figured out the answer.

I inserted following code in the applicable sheet and it is working fine.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Application.Intersect(Target, Range("A1:W1, D2:W3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "Inserting Column or editing top 3 cells in this column is not allowed." & vbNewLine & "First 3 columns are editable or after column 'W' columns can be added.", 48, "Protected cells."
Application.Undo
Application.EnableEvents = True

End Sub