Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel macro to delete/add rows on protected sheet

Posted on 2009-12-16
8
Medium Priority
?
693 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:JonYen
  • 4
  • 4
8 Comments
 
LVL 7

Expert Comment

by:harr22
ID: 26065337
is the sheet password protected and if so do you know the password?
0
 
LVL 7

Expert Comment

by:harr22
ID: 26065472
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
 

Author Comment

by:JonYen
ID: 26065764
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:harr22
ID: 26065861
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
 

Author Comment

by:JonYen
ID: 26066500
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
 

Author Comment

by:JonYen
ID: 26066521
sorry i couldn't add the previous sheet as it contained macros which obviously isn't allowed!
Engraving-Details-MASTER-FORM.xlsx
0
 
LVL 7

Accepted Solution

by:
harr22 earned 2000 total points
ID: 26067976
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
 

Author Closing Comment

by:JonYen
ID: 31666850
superb solution.  har22 was very helpful & patient expert
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question