Copy hidden row and insert

LeahWright

New Member
Joined
Jun 14, 2004
Messages
39
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.)
Code:
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.)
Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

LeahWright

New Member
Joined
Jun 14, 2004
Messages
39
Note: I don't necessarily need to password protect, but I do need to protect to prevent accidental changes.
 

LeahWright

New Member
Joined
Jun 14, 2004
Messages
39
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

Active Member
Joined
Apr 9, 2002
Messages
392

ADVERTISEMENT

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

New Member
Joined
Jun 14, 2004
Messages
39
With a slight modification to copy the entire row 1, it seem to be working perfectly. Thank you!
 
Last edited:

Padawan

Active Member
Joined
Apr 9, 2002
Messages
392
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

New Member
Joined
Jun 14, 2004
Messages
39
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". :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

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