how to insert row and copy down formula in a Protected Sheet

[AndrewMartin]

Hi, first post on here so please excuse! I am, and have been for quite some time, trying to set up a spreadsheet to be used by a wide range of users (many with very little excel knowledge) which has a great deal of code and formula within it. I have therefore password protected the sheet to avoid issues with people deleting the formulas etc. However, the sheet I need help with is one where I want around 25 rows to be available to edit for normal use, but, on occasion, people will need additional rows (on some schemes up to 150 rows), so I need an easy way for people to add rows and copy down the formula from the row above, without the sheet being unprotected.

I have tried setting up a macro to add a row and this appeared to work great as it was unlocking the sheet, carrying out the necessary inserting and copying, and then protecting the sheet again (albeit with no password but beggars cant be choosers eh?). The problem with this in practice was that the macro always added the row to say row 26, so if you had added 10no. rows and filled these in and then needed more rows, by clicking the macro button it added the new row in above completed rows, ruining the sequencing.

I then tried setting up groups of 20no. rows and minimising these, therefore only a single row was visible for each group at the bottom of the sheet, and could be expanded when needed, however when the sheet is locked the 'expand' function was also locked and I cant see how to get this aspect unlocked without unprotecting!

So, what I need is something to either allow the 'group' command to be available for use when the sheet is protected, or something else to carry out the function I need that you wonderfully clever people out there know about!

Many thanks!

Andrew

 

[Mick Peters]

Hi Andrew, I am by no means an expert and am on here more often than not getting help not giving it. I just wondered though if you had tried using the option on the protect menu? When you protect the sheet you see a menu and on that is insert Rows and insert Columns. If you tick these boxes then although the sheet is protected the user or your macro are allowed to insert either of those.

I hope this helps
Mick.

 

[AndrewMartin]

Many thanks for the reply Mick, unfortunately, although you can allow users to add rows from the protect menu, this doesn't copy down the formulas from cells above, and the copy down function seems to always be locked no matter what selections are made on the protect menu! Very frustrating!
Thanks very much for the thoughts though, appreciate it!
Andrew

 

[Mick Peters]

So If you can use the menu to allow the insertion of a row then you should be able to use a macro or VBA to copy the formulas and then paste them in where you want them. I will have a look later as I am sure I have done something similar but I am not sure if I used the VBA to remove the protection do the copy and then put it back on or if I just copied and pasted down into an already blank row. Either way it should be possible.

Many thanks for the reply Mick, unfortunately, although you can allow users to add rows from the protect menu, this doesn't copy down the formulas from cells above, and the copy down function seems to always be locked no matter what selections are made on the protect menu! Very frustrating!
Thanks very much for the thoughts though, appreciate it!
Andrew

 

[Sharid]

Not sure if this is what you are looking for, but here is what it does. It will copy row 9 from A to CG and then paste it into the next blank row.

A box will open it will tell the user to enter the number of rows that they want e.g 1 to 1000+ up to them it will then copy row 9 and past it as many time as the user has requested.

If you have all your formulas in row 9, it will copy it. you can set the code to input the sheet password to unlock the sheet before it copies and pastes and then relock the sheet.

You can change the source row e.g row 9 to what ever you want, also change the sheet name where you want the row pasted.

Public Sub CommandButton3_Click()
Dim Rng
Rng = InputBox("Enter number of rows required.")
If IsNumeric(Rng) Then
    For i = 1 To Rng
 'Change source row
    Range("a9:cg9").Copy _
 'Change sheet name
    Destination:=Sheets("Active Caseload").Range("a65536").End(xlUp).Offset(1)
    Next i
Else
    MsgBox "As no row amount was entered, no rows have been added"
End If
End Sub

 

[Sharid]

Try this with a password protection

Private Sub CommandButton1_Click()

'Change your password here
Sheet1.Unprotect "Password"

Dim Rng
Rng = InputBox("Enter number of rows required.")
If IsNumeric(Rng) Then
    For i = 1 To Rng
 'Change source row and sheet name
    Range("a2:cg2").Copy _
    Destination:=Sheets("Sheet1").Range("a65536").End(xlUp).Offset(1)
    Next i
Else
    MsgBox "As no row amount was entered, no rows have been added"
    
End If

'Change your password here
Sheet1.Protect "Password"

End Sub

 

[AndrewMartin]

Hi JUBA - thanks very much for the reply - Just getting a chance to look at this!

I am not hugely familiar with code etc but dabbled a bit - What I have done is the following (please let me know if incorrect!):

Opened the file and clicked on the appropriate sheet (in this case named 'CE REGISTER'). Clicked on the developer tab and then Visual Basic button to open the VBA and then double clicked on the sheet I need the code for (in this case Sheet6(CE REGISTER)). I then pasted the code you had provided above, with the following changes - name changed to add_rows (from CommandButton1_Click), range changed to a35:v35 to take the information from row 35, sheet name changed, changed the password (just to Password1 for now!). Not sure what the Range("a65536") relates to - does this need to be changed? The code I now have is below:

Private Sub new_rows()
'Change your password here
Sheet1.Unprotect "Password1"
Dim Rng
Rng = InputBox("Enter number of rows required.")
If IsNumeric(Rng) Then
For i = 1 To Rng
'Change source row and sheet name
Range("a35:v35").Copy _
Destination:=Sheets("CE REGISTER").Range("a65536").End(xlUp).Offset(1)
Next i
Else
MsgBox "As no row amount was entered, no rows have been added"

End If
'Change your password here
Sheet1.Protect "Password1"
End Sub


So, at this point, how do I run this code? Previously I have recorded a macro and assigned a button to this which is easy for others to use, so just need something (as in a button) to be able to be run this code. Hoping you can help on this and it looks like what I am hoping to do! Tried using the play button within VB but came up with a run time error '1004': application defined or object defined error?

Help please!

Thanks

Andrew

 

[Mick Peters]

Hi Andrew you can activate the code from a button if you wish by first unprotecting your sheet, then go to the developer tab and select insert and click on the command button (tope left of the menu). Then your curser will display a cross. Draw the button the size you want it. When you release it you will see a pop up menu. Select new and you will see the VBA code box. Add the code you have put here into that box deleting whatever is already in it for that button. Once you are finished close the VBA window and right click your button. You can then add a name to it. if you then protect your sheet again you can test.
I hope this helps
Mick.

 

[Sharid]

You can down load an example here, i did for some one else. This version is without the sheet protection, you will need to add that to the code

http://www.mrexcel.com/forum/excel-questions/964865-button.html

 

[MARK858]

Not sure what the Range("a65536") relates to - does this need to be changed?

Range("a65536") is the last cell in column A in older versions of Excel, it is better to use Rows.Count as it uses the last cell in whichever version of Excel is being used (2007 and later versions have 1,048,576 rows rather than 65,536).

The code JUBA posted is using it to look upwards until it finds the last used cell in column A then puts the "pasted" data in the row below.

The IsNumeric test isn't really necessary unless you do want the message (there are other ways of doing this if necessary) as if it is declared correctly as Long it would error out and you can tell the InputBox to only accept numbers.


As per the above it could be written as...

Private Sub new_rows()
    'Change your password here
    Sheet1.Unprotect "Password1"
    Dim Rng [COLOR="#FF0000"]As Long, i As Long[/COLOR]
    [COLOR="#FF0000"]Rng = Application.InputBox("Enter number of rows required.", Type:=1)[/COLOR]

        For i = 1 To Rng
            'Change source row and sheet name
            Sheet1.Range("a35:v35").Copy _
                    Destination:=Sheets("CE REGISTER").[COLOR="#FF0000"]Range("A" & Rows.Count).[/COLOR]End(xlUp).Offset(1)
        Next i
  
    'Change your password here
    Sheet1.Protect "Password1"
End Sub

I hope that explains it a bit.

Can you run the code above and see if you still get the

VB but came up with a run time error '1004': application defined or object defined error?

error again and if so what line is highlighted?

P.S.
As JUBA wrote in post #5...

Not sure if this is what you are looking for

I am also still not 100% sure by your description that it is what you are after but you haven't stated any contrary so far.

Edit: Obviously still look at the link JUBA posted in post #9 if you haven't looked at/missed it.