Copy hidden row and insert

[LeahWright]

I am working on a timesheet that will allow the user to input data, but not change certain cells. I have two buttons built into the worksheet: one to delete a row and one to insert a row. The buttons delete and insert in relation to the location of the active cell.

My problem: I am getting 1004 errors, as well as others at other times.


Since the line I want to insert involves specific formatting and merging, I thought the easiest way to go about this would be to make a "line master" worksheet and copy the line from the line master and insert it into the input sheet.

Ideally:
Insert Button:
1. User clicks a cell in the row on ws."Input" where they want the new row to appear.
2. Unprotect (with password) ws."Line Master" and ws."Input"
3. Unhide ws."Line Master"
4. Copy Row 1:1 from ws."Line Master"
5. Go back to ws."Input" and insert the copied row at the location of the ActiveCell from Step 1
6. Hide ws."Line Master"
7. Protect (with password) ws."Line Master" and ws."Input"

My code: (I was having problems, so I stripped the protect/unprotect/hide/unhide lines from the code and went back to basics.)

Sub InsertRow()
    Sheets("Line Master").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("Input").Select
Rows(ActiveCell.Row).Insert Shift:=xlDown

Delete Button:
1. User clicks a cell in the row on ws."Input" they want to delete.
2. Unprotect (with password) ws."Input"
3. Delete the row.
4. Protect (with password) ws."Input"

The delete code I'm currently using, thanks to MrExcel.com : (Except for the lack of protect/hide code, it seems to be working perfectly.)

Sub delete_row()
'Erik Van Geit
'050608 0041
'to delete rows when sheet is protected

Dim nrs As String

If Selection.Rows.Count = 1 Then
nrs = "row " & Selection.Row
Else
nrs = "rows " & Application.WorksheetFunction.Substitute(Selection.EntireRow.Address(0, 0), ":", " to ")
End If

ActiveSheet.Unprotect
If MsgBox("Do you want to delete " & nrs & "?", 36, "DELETE ROWS") = vbNo Then Exit Sub
Selection.EntireRow.Delete
ActiveSheet.Protect
End Sub

Can someone please help? I want to be able to protect/hide but still allow the user to add/delete lines by using the buttons I've supplied (but not the menus). I can't seem to tie the protection lines in with the rest of my code and make it work.

 

[LeahWright]

Note: I don't necessarily need to password protect, but I do need to protect to prevent accidental changes.

 

[LeahWright]

Any input? Anyone? BUMP!

 

[LeahWright]

Just bumping once more before bed in hopes that someone answers and leaves me a gift to find in the morning, much like the tooth fairy.

 

[Padawan]

LeahWright

I changed a little bit of your insert code. This assumes the selection you want to copy is in cell A1.

Change the password as required.


Private Sub CommandButton1_Click()

Sheets("Line Master").Unprotect Password:="Password"

Sheets("Input").Select

ActiveSheet.Unprotect Password:="Password"

Rows(ActiveCell.Row).Insert Shift:=xlDown

Sheets("Line Master").Range("A1").Copy ActiveCell

ActiveSheet.Protect Password:="Password"

Sheets("Line Master").Protect Password:="Password"


End Sub



For the Delete button, try this.



Private Sub CommandButton2_Click()
'Sub delete_row()
'Erik Van Geit
'050608 0041
'to delete rows when sheet is protected

Dim nrs As String

If Selection.Rows.Count = 1 Then
nrs = "row " & Selection.Row
Else
nrs = "rows " & Application.WorksheetFunction.Substitute(Selection.EntireRow.Address(0, 0), ":", " to ")
End If

ActiveSheet.Unprotect Password:="Password"

If MsgBox("Do you want to delete " & nrs & "?", 36, "DELETE ROWS") = vbNo Then Exit Sub
Selection.EntireRow.Delete

ActiveSheet.Protect Password:="Password"
End Sub


Hope this helps.

 

[LeahWright]

With a slight modification to copy the entire row 1, it seem to be working perfectly. Thank you!

 

[Padawan]

LeahWright

I had trouble getting the whole row to copy properly. That's why I had changed it to one cell.

But I'm glad it worked for you.

 

[LeahWright]

It seems to be okay. I changed it to 1:1. I'm sure I'll be crying for help if it starts messing up. I have someone testing the timesheet later today to "see if she can break it".