Excel macro to delete/add rows on protected sheet

Hi Experts,

I need to create 2 macro buttons on a protected Excel spreadsheet.

Macro 1:  Finish
"      Insert a button called Finish
"      This should remove the protection from the sheet.
"      Trigger the macro to find the last row of data and delete all empty rows below it.
"      Re-instate the protection and go back to cell A1 & save.

Macro 2  ADD ROW

"      Insert button called Add Row
"      This should remove the protection from the sheet.
"      Trigger the macro to add a row at the end of the spreadsheet and copy the formulas down.
"      Re-instate the protection and go back to cell A1 & save

If this is not possible to do because of the protection, please can you let me know how I would go about this with the protection off.

Regards
Jon

JonYenAsked:
Who is Participating?
 
harr22Connect With a Mentor Commented:
how about this
Private Sub add_row_button_Click()
    ActiveSheet.Unprotect '"password"
    Range("A65536").End(xlUp).Range("A1:J4").Copy
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    ActiveSheet.Protect '"password"
    ActiveWorkbook.Save
End Sub

Open in new window

0
 
harr22Commented:
is the sheet password protected and if so do you know the password?
0
 
harr22Commented:
How about this?
Private Sub Finish_Click()
    ActiveSheet.Unprotect '"password"
    Range("A65536").End(xlUp).Offset(1, 0).EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Protect '"password"
    ActiveWorkbook.Save
End Sub
 
Private Sub AddRow_Click()
    ActiveSheet.Unprotect '"password"
    Range("A65536").End(xlUp).EntireRow.Copy
    Range("A65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial
    Application.CutCopyMode = False
    ActiveSheet.Protect '"password"
    ActiveWorkbook.Save
End Sub

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JonYenAuthor Commented:
Hi Har22,

Sorry if I sounded more technical than I actually am!  Thanks for the script but can you explain to me how I use it in my spreadsheet?  I got as far as right clicking the worksheet tab and selecting View Code but I don't really know where to go from there!

Regards
Jon
0
 
harr22Commented:
First you need to create the ActiveX control buttons
Excel 2007:
http://office.microsoft.com/en-us/excel/HP102366761033.aspx

Previous Versions:
http://office.microsoft.com/en-us/excel/HP051984631033.aspx?pid=CH062528021033

Once thats done turn on design mode from the control (or developer in 2007) toolbar, then double click the button to get to its source code and paste the two click events into the perspective buttons.

If you have trouble, post your sheet.  I'll add it and post it back.

Travis
0
 
JonYenAuthor Commented:
thanks Har22.  that seems to have got it for the Delete button.  for some reason the Add Row button is not working though.  

it seems to have the idea because it copies the formating from a a single cell at the end of the final row down.

i hope you don't mind but i have taken the liberty of attaching my sheet as I can't explain it very well.

regards
Jon
0
 
JonYenAuthor Commented:
sorry i couldn't add the previous sheet as it contained macros which obviously isn't allowed!
Engraving-Details-MASTER-FORM.xlsx
0
 
JonYenAuthor Commented:
superb solution.  har22 was very helpful & patient expert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.