Lock Header Rows from Editing

IS there a way, either in VBA or just excel to lock the header rows (Rows
1 -3) so that they can not be Edited or deleted?

Select all the cells in your worksheet, go to the Format menu,
choose Cells, then the Protection tab. There, uncheck the Locked
property and click OK. Now select rows 1:3, go back to the
Protection tab, check the Protection tab, and click OK. Finally,
go to the Tools menu, choose Protection, and Protect Sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"HotRod" <[email protected]> wrote in message
news:%[email protected]...
> IS there a way, either in VBA or just excel to lock the header
> rows (Rows 1 -3) so that they can not be Edited or deleted?
>

I found that when I did this some of my VBA code was not working properly,
specifically trying to change the interior ROW colour.



"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> Select all the cells in your worksheet, go to the Format menu, choose
> Cells, then the Protection tab. There, uncheck the Locked property and
> click OK. Now select rows 1:3, go back to the Protection tab, check the
> Protection tab, and click OK. Finally, go to the Tools menu, choose
> Protection, and Protect Sheet.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "HotRod" <[email protected]> wrote in message
> news:%[email protected]...
>> IS there a way, either in VBA or just excel to lock the header rows (Rows
>> 1 -3) so that they can not be Edited or deleted?
>>
>
>

You can protect your sheet to do this. However, cells are locked by default
so you need to highlight the entire sheet except for the first three rows
(you can do this by highlighting the fourth row and pressing ctrl+shift+down)
and uncheck locked on the protection tab of cell properties. You can protect
the sheet by accessing Tools->Protection->Protect Sheet from the menu.
You'll get a dialog showing all the available options, and you can add a
password which has to be entered to unlock the sheet. You should note,
however, that if you prevent users from deleting rows (to protect the first
three), they won't be able to delete any rows in the sheet.

"HotRod" wrote:

> IS there a way, either in VBA or just excel to lock the header rows (Rows
> 1 -3) so that they can not be Edited or deleted?
>
>
>

What I did was highlight the whole sheet and "Unlocked" it then I
highlighted the first three rows and "Locked" them, finally I protected the
whole workbook. However when I ran my VBA macro it will not allow me to
change the interior colour of a row. As soon as I unlock the sheet
everything is fine.

You can modify your code to unprotect the worksheet, make the changes, then
reprotect the worksheet.

HotRod wrote:
>
> I found that when I did this some of my VBA code was not working properly,
> specifically trying to change the interior ROW colour.
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
> > Select all the cells in your worksheet, go to the Format menu, choose
> > Cells, then the Protection tab. There, uncheck the Locked property and
> > click OK. Now select rows 1:3, go back to the Protection tab, check the
> > Protection tab, and click OK. Finally, go to the Tools menu, choose
> > Protection, and Protect Sheet.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> > "HotRod" <[email protected]> wrote in message
> > news:%[email protected]...
> >> IS there a way, either in VBA or just excel to lock the header rows (Rows
> >> 1 -3) so that they can not be Edited or deleted?
> >>
> >
> >

--

Dave Peterson

Any chance you have an example of this code to Protect and Unprotect the
sheet???



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You can modify your code to unprotect the worksheet, make the changes,
> then
> reprotect the worksheet.
>
> HotRod wrote:
>>
>> I found that when I did this some of my VBA code was not working
>> properly,
>> specifically trying to change the interior ROW colour.
>>
>> "Chip Pearson" <[email protected]> wrote in message
>> news:[email protected]...
>> > Select all the cells in your worksheet, go to the Format menu, choose
>> > Cells, then the Protection tab. There, uncheck the Locked property and
>> > click OK. Now select rows 1:3, go back to the Protection tab, check the
>> > Protection tab, and click OK. Finally, go to the Tools menu, choose
>> > Protection, and Protect Sheet.
>> >
>> >
>> > --
>> > Cordially,
>> > Chip Pearson
>> > Microsoft MVP - Excel
>> > Pearson Software Consulting, LLC
>> > www.cpearson.com
>> >
>> > "HotRod" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> IS there a way, either in VBA or just excel to lock the header rows
>> >> (Rows
>> >> 1 -3) so that they can not be Edited or deleted?
>> >>
>> >
>> >
>
> --
>
> Dave Peterson

Option explicit
sub testme()
with worksheets("Sheet99")
.unprotect password:="topsecret"
'do your formatting
.protect password:="topsecret"
end with
end sub



HotRod wrote:
>
> Any chance you have an example of this code to Protect and Unprotect the
> sheet???
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You can modify your code to unprotect the worksheet, make the changes,
> > then
> > reprotect the worksheet.
> >
> > HotRod wrote:
> >>
> >> I found that when I did this some of my VBA code was not working
> >> properly,
> >> specifically trying to change the interior ROW colour.
> >>
> >> "Chip Pearson" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Select all the cells in your worksheet, go to the Format menu, choose
> >> > Cells, then the Protection tab. There, uncheck the Locked property and
> >> > click OK. Now select rows 1:3, go back to the Protection tab, check the
> >> > Protection tab, and click OK. Finally, go to the Tools menu, choose
> >> > Protection, and Protect Sheet.
> >> >
> >> >
> >> > --
> >> > Cordially,
> >> > Chip Pearson
> >> > Microsoft MVP - Excel
> >> > Pearson Software Consulting, LLC
> >> > www.cpearson.com
> >> >
> >> > "HotRod" <[email protected]> wrote in message
> >> > news:%[email protected]...
> >> >> IS there a way, either in VBA or just excel to lock the header rows
> >> >> (Rows
> >> >> 1 -3) so that they can not be Edited or deleted?
> >> >>
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson