Worksheet protection, going beyond inserting rows
 

Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows (which
needs no explanation) but allow the user to edit within the inserted rows
while still keeping the pre-existing data locked. Can I do that? If so, how?

Thanks!

Worksheet protection, going beyond inserting rows
 

You can do that only with VBA. Basically, you would use a Worksheet_Change
macro to pick up on the fact that a row has been added and which row it was.
Then the code can unprotect the sheet, unlock all the cells in that one row,
and protect the sheet. You would have to come up with some trigger to fire
the code to again lock the cells in that row when you want them locked. The
macro below fires when a row is inserted and unlocks the cells in that row.
Place this macro in the sheet module of your sheet. To access that module,
right-click on the sheet tab and select View Code. Paste this macro to that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows
(which
needs no explanation) but allow the user to edit within the inserted rows
while still keeping the pre-existing data locked. Can I do that? If so,
how?

Thanks!

Worksheet protection, going beyond inserting rows
 

Otto;

That is great! I just need to figure out a way to reset the protection to
allow inserting new rows without having to unprotect and reprotect.
I am halfway there....
Thanks! that did help.

"Otto Moehrbach" wrote:

You can do that only with VBA. Basically, you would use a Worksheet_Change
macro to pick up on the fact that a row has been added and which row it was.
Then the code can unprotect the sheet, unlock all the cells in that one row,
and protect the sheet. You would have to come up with some trigger to fire
the code to again lock the cells in that row when you want them locked. The
macro below fires when a row is inserted and unlocks the cells in that row.
Place this macro in the sheet module of your sheet. To access that module,
right-click on the sheet tab and select View Code. Paste this macro to that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows
(which
needs no explanation) but allow the user to edit within the inserted rows
while still keeping the pre-existing data locked. Can I do that? If so,
how?

Thanks!

Worksheet protection, going beyond inserting rows
 

Otto;

As I am below a Jr. Level programmer, my workaround is not as cool as the
solution you provided. However, for anyone that may be in a similier
situation....

I applied your solution which works great. Then, to sort of, "reset" the
sheet, I simply recorded a macro applying a keyboard shortcut.
Thanks Again!

"Otto Moehrbach" wrote:

You can do that only with VBA. Basically, you would use a Worksheet_Change
macro to pick up on the fact that a row has been added and which row it was.
Then the code can unprotect the sheet, unlock all the cells in that one row,
and protect the sheet. You would have to come up with some trigger to fire
the code to again lock the cells in that row when you want them locked. The
macro below fires when a row is inserted and unlocks the cells in that row.
Place this macro in the sheet module of your sheet. To access that module,
right-click on the sheet tab and select View Code. Paste this macro to that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows
(which
needs no explanation) but allow the user to edit within the inserted rows
while still keeping the pre-existing data locked. Can I do that? If so,
how?

Thanks!

Worksheet protection, going beyond inserting rows
 

For my edification, what did you mean when you said you "reset" the sheet?
Otto
"Wh0079" wrote in message
...
Otto;

As I am below a Jr. Level programmer, my workaround is not as cool as the
solution you provided. However, for anyone that may be in a similier
situation....

I applied your solution which works great. Then, to sort of, "reset" the
sheet, I simply recorded a macro applying a keyboard shortcut.
Thanks Again!

"Otto Moehrbach" wrote:

You can do that only with VBA. Basically, you would use a
Worksheet_Change
macro to pick up on the fact that a row has been added and which row it
was.
Then the code can unprotect the sheet, unlock all the cells in that one
row,
and protect the sheet. You would have to come up with some trigger to
fire
the code to again lock the cells in that row when you want them locked.
The
macro below fires when a row is inserted and unlocks the cells in that
row.
Place this macro in the sheet module of your sheet. To access that
module,
right-click on the sheet tab and select View Code. Paste this macro to
that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows
(which
needs no explanation) but allow the user to edit within the inserted
rows
while still keeping the pre-existing data locked. Can I do that? If so,
how?

Thanks!

Worksheet protection, going beyond inserting rows
 

Well, in following your instructions I added your code to each of the sheets
I wanted this to work on... in VBA via copy and paste.
I was able to insert one or more rows and then edit only the rows I inserted
which is precisely what I needed. However, if I tried to insert another row
after editing. The option from the flyout menu is grayed out.
I tried a few statements in the same sub-routine to allow inserting rows
after one time however, like I said.... VERY little programming knowledge....
hopefully I can change that. ANYWAY, in order for me to allow further editing
I just recorded a macro that goes to unprotect then protect the sheet.....
also allowing to insert rows as for some reason, that option gets un-checked.
Still, your code is what will really allow me to edit without concern of
screwing up existing formulas which is the point after all.
Thanks!


"Otto Moehrbach" wrote:

For my edification, what did you mean when you said you "reset" the sheet?
Otto
"Wh0079" wrote in message
...
Otto;

As I am below a Jr. Level programmer, my workaround is not as cool as the
solution you provided. However, for anyone that may be in a similier
situation....

I applied your solution which works great. Then, to sort of, "reset" the
sheet, I simply recorded a macro applying a keyboard shortcut.
Thanks Again!

"Otto Moehrbach" wrote:

You can do that only with VBA. Basically, you would use a
Worksheet_Change
macro to pick up on the fact that a row has been added and which row it
was.
Then the code can unprotect the sheet, unlock all the cells in that one
row,
and protect the sheet. You would have to come up with some trigger to
fire
the code to again lock the cells in that row when you want them locked.
The
macro below fires when a row is inserted and unlocks the cells in that
row.
Place this macro in the sheet module of your sheet. To access that
module,
right-click on the sheet tab and select View Code. Paste this macro to
that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert rows
(which
needs no explanation) but allow the user to edit within the inserted
rows
while still keeping the pre-existing data locked. Can I do that? If so,
how?

Thanks!

Worksheet protection, going beyond inserting rows
 

You're welcome. Thanks for the feedback. Otto
"Wh0079" wrote in message
...
Well, in following your instructions I added your code to each of the
sheets
I wanted this to work on... in VBA via copy and paste.
I was able to insert one or more rows and then edit only the rows I
inserted
which is precisely what I needed. However, if I tried to insert another
row
after editing. The option from the flyout menu is grayed out.
I tried a few statements in the same sub-routine to allow inserting rows
after one time however, like I said.... VERY little programming
knowledge....
hopefully I can change that. ANYWAY, in order for me to allow further
editing
I just recorded a macro that goes to unprotect then protect the sheet.....
also allowing to insert rows as for some reason, that option gets
un-checked.
Still, your code is what will really allow me to edit without concern of
screwing up existing formulas which is the point after all.
Thanks!


"Otto Moehrbach" wrote:

For my edification, what did you mean when you said you "reset" the
sheet?
Otto
"Wh0079" wrote in message
...
Otto;

As I am below a Jr. Level programmer, my workaround is not as cool as
the
solution you provided. However, for anyone that may be in a similier
situation....

I applied your solution which works great. Then, to sort of, "reset"
the
sheet, I simply recorded a macro applying a keyboard shortcut.
Thanks Again!

"Otto Moehrbach" wrote:

You can do that only with VBA. Basically, you would use a
Worksheet_Change
macro to pick up on the fact that a row has been added and which row
it
was.
Then the code can unprotect the sheet, unlock all the cells in that
one
row,
and protect the sheet. You would have to come up with some trigger to
fire
the code to again lock the cells in that row when you want them
locked.
The
macro below fires when a row is inserted and unlocks the cells in that
row.
Place this macro in the sheet module of your sheet. To access that
module,
right-click on the sheet tab and select View Code. Paste this macro
to
that
module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = Columns.Count Then
If Target(1).Row = Target(Target.Count).Row Then
ActiveSheet.Unprotect
Target.Locked = False
ActiveSheet.Protect
End If
End If
End Sub
"Wh0079" wrote in message
...
Hello;

I performed a cursory search and could not locate this scenario.

If I want to protect a sheet but allow a user to not only insert
rows
(which
needs no explanation) but allow the user to edit within the inserted
rows
while still keeping the pre-existing data locked. Can I do that? If
so,
how?

Thanks!