Insert Column greyed out as column contains locked cells

mrbougles

New Member
Joined
Jan 27, 2012
Messages
7
Hi,

I have got an excel spreadsheet that I have some rows protected in (utilising a macro). Because of the protected rows I can no longer insert new columns as the option is greyed out (because I have protected it against selecting some of the rows) when I right click a column header.

Is there some way that I can modify my macro to allow me to select a column that has protected cells in it? if thats not possible a macro to insert new columns when i click some button would do the job.

My macro is currently like this...

Password = ""
ActiveSheet.Unprotect Password
InsertRowsAndFillFormulas
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

End Sub

many thanks,

Hugh
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mrbougles

New Member
Joined
Jan 27, 2012
Messages
7
Thanks for the quick response but I dont think that its.

The macro is working pretty well as is and is 99% there.

I click a button and the macro unlocks my sheet, then runs some code (insertrowandfill), then locks it again. When it locks it it stops me from selecting locked cells but still lets me do everything else pretty much.

The only thing is is that because I cant select a locked cell I cans select any row/column or insert any row/column because all rows/columns contain locked cells.

I need my code to allow me to not select a locked&protected cell but allow me to select a column containing a locked&protected cell so that I can insert a column. unless its my something else...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,361
Messages
5,641,653
Members
417,229
Latest member
BODYCOTE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top