Insert new row in excel which is protected

[BMD4]

Hello,

I have excel with 3 columns which are unlocked and 1 data column which is locked and the sheet is protected.


Desc A1 A2 Total
JL1 20 20 40
JL4 20 10 30


When I try to insert new row JL2, I do not see insert option enabled.
Is there any way, I can insert new row in a protected excel with data columns unlocked and formula column locked.



Thanks in advance

[Logit]

.
I believe you also need to unprotect the sheet.

[Paul_Hossler]

What happens when you "Allow User to Insert Rows" when you protect the sheet?

22627

[BMD4]

Hi Paul,

I tried selecting 'Insert Rows' option while protecting sheet. But it still doesn't work.

[BMD4]

Hello,

I need to insert row with protection. As this will be done by user, I cannot unprotect sheet.

[p45cal]

Since you're in the VBA code help section, are you using code to do this? If so,what is the code currently? We'll help you tweak it.

[BMD4]

Hi,

I did use VB code which I got from one of the forums.

But,this code is deleting value (when I select value and click on the insert button) from existing table instead of creating new row


Sub Button1_Click()
Dim pswStr As String
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
Range("Table1[[#Headers],[Total]]").Select
Selection.End(xlDown).Select
Selection.Offset(1, -4).Select
ActiveCell.FormulaR1C1 = "new"
ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Selection.ClearContents
Application.ScreenUpdating = True

End Sub

[p45cal]

Take out the On Error resume Next lineand step throught the code using the F8 key on the keyboard. Does an error occur?If so, which line?

[p45cal]

cross posted elsewhere

[BMD4]

Take out the On Error resume Next lineand step throught the code using the F8 key on the keyboard. Does an error occur?If so, which line?

Hi,

I get error, 'Method 'Range' of object '_Global' failed at this line
Range("Table1[[#Headers],[tot]]").Select

[p45cal]

It's bedtime for me now.
I expected an error there.
It's difficult to work out what you're trying to do while working blind. Supply a simple workbook with such a table in and the code and explain what you're trying to do.
Also Range("Table1[[#Headers],[total]]").Select is different (red). Which is correct?
Finally, I won't respond at all unless you supply links in this thread to everywhere you've cross posted this question to. Why? Have a read of http://www.excelguru.ca/content.php?184

[david000]

Is there any way, I can insert new row in a protected excel with data columns unlocked and formula column locked.


Try unlocking the cells first.

To insert a row on a protected sheet all of the cells in that row need to be unlocked.



Sub Lock_Formulas_Only()
On Error GoTo Msg
With ActiveSheet.Cells
.Locked = False
MsgBox "Cells are Unlockled."
.SpecialCells(xlCellTypeFormulas, 23).Locked = True
End With
Exit Sub
Msg:
MsgBox Err.Description
End Sub