Granting permission to insert row in a protected file

[brockk]

Greetings to all in the forum. I have a worksheet that tracks the requisitions made by the dept. and for obvious reasons (formulas), the worksheet is password protected. But, I would like to give access to actually INSERT ROWS ONLY, as the need for this option is truely necessary. The majority of the requisitions only require just one P.O., but, there are a few that might need various P.O.'s to complete the requisition being that it may require various suppliers (and this is why I need to grant the permission to insert rows).

sample of this is the following:

date req.# descrip. p.o.# status
1/1 01 abc 01 ok
1/4 02 bcd 02 ok
1/6 03 cde 03 ok
** ** *** 04 backorder
** ** *** 05 pickup
1/9 04 def 06 ok


On the date of 1/6, req. #03 required 3 p.o.'s to complete the requisition. The process required to insert 2 rows to include p.o. #'s 04 and 05. This is where I get stuck at, don't know how to give access to users to ONLY insert rows as to not alter the data. Any help is appreciated.

 

[brockk]

I'm wondering if this could be possible or not through macro..... any suggestions? Thanks in advance.

 

[ColinKJ]

Hi brockk,

You could insert the following in a standard moduel

Code:

Sub InsertRow()
ActiveSheet.Unprotect Password = "ckj"
Selection.EntireRow.Insert
ActiveSheet.Protect Password = "ckj"
End Sub

Code:

(Use your own Password)

Either assign the macro to a Key combination through Tools>Macro>Macro>Options

or just run it direct through Tools>Macro>Macro then Run.

Because in the above macro you expose your password, if the user wanted to look at your VBA Project, therefore you would need to Password Protect your VBA Project.

ColinKJ

 

[brockk]

Thanks for your reply ColinKJ, just wondering one thing......

How would the macro know where to insert the row. I mean, would the user need to place the cursor under, over the exact place where they would want to insert the row? Because, I wouldn't want them to actually have the capability to delete any information accidently if an error occurred. Sorry, for being so picky with this issue but as you might imagine, several people actually work at the same time with the worksheet and anything is possible. Thanks again.

 

[ColinKJ]

Hi brockk,

As it is, the user needs to select a cell in the row where they want the row inserted.

The selected row will be moved down one row.

Colin

 

[brockk]

ok, thanks for the info. I'll try it and let you know how it turned out. Once again, thanks for the heads up on this one.

 

[brockk]

I just tried out the macro and as for inserting a row, it worked like a charm but, I noticed that once I ran the macro, everything got protected and I was unable to add any text in all of the cells. Also, I changed the "password" and now I can't unprotect the sheet to add data.

I guess I certainly did something wrong. LOL. Any suugestions or just start over from scratch. Thanks.

 

[ColinKJ]

If you've forgotten the password you will need to start over.

You should still be able to copy and paste your data to a new sheet.

ColinKJ

 

[brockk]

ok, will try again. Thanks.