Protected spreadsheet, but need to add rows

[davegb]

I know that if I protect a spreadsheet, new rows can't be added. I'm
building a new spreadsheet for some of our less-knowledgeable users. I
only want them to be able to enter data, but this means they have to be
able to add rows for new/additional data. My plan is to create a macro
that prompts them to select the line above which they want to insert
new rows, tell it how many rows they want to add (1-10), then unprotect
the worksheet, add the new rows, adjust the sums to include the new
rows, if neccessary, then reprotect the spreadsheet.

I'm interested in feedback on:

Is this the best way to do this, or is there a better way to allow them
access but protect the cells with text and formulas?

If this is the best way, is there anything I'm missing? Any tricks
you've learned from the school of hard knocks?

Thanks in advance.

[renegan]

If you have Excel 2003, you can leave a row unlocked wherever you want and tell them to highlight the row below that one and click insert. That way they can enter their data and rest of the worksheet is safe. When you protect just select "Select locked cells", "Select unlocked cells", "Insert Rows". I don't think excel has this function in earlier versions.

[Ron de Bruin]

Hi renegan

Excel 2002 is the first version with this option

--
Regards Ron de Bruin
http://www.rondebruin.nl


"renegan" <[email protected]> wrote in message
news:[email protected]...
>
> If you have Excel 2003, you can leave a row unlocked wherever you want
> and tell them to highlight the row below that one and click insert.
> That way they can enter their data and rest of the worksheet is safe.
> When you protect just select "Select locked cells", "Select unlocked
> cells", "Insert Rows". I don't think excel has this function in earlier
> versions.
>
>
> --
> renegan
> ------------------------------------------------------------------------
> renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
> View this thread: http://www.excelforum.com/showthread...hreadid=535129
>

[Kletcho]

You could look at using Excel's built in form (data -- forms).

[davegb]

Ron de Bruin wrote:
> Hi renegan
>
> Excel 2002 is the first version with this option
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl

Thanks to both of you. I didn't know the newer versions had this
option. But I'm using XL2000 anyway.

>
>
> "renegan" <[email protected]> wrote in message
> news:[email protected]...
> >
> > If you have Excel 2003, you can leave a row unlocked wherever you want
> > and tell them to highlight the row below that one and click insert.
> > That way they can enter their data and rest of the worksheet is safe.
> > When you protect just select "Select locked cells", "Select unlocked
> > cells", "Insert Rows". I don't think excel has this function in earlier
> > versions.
> >
> >
> > --
> > renegan
> > ------------------------------------------------------------------------
> > renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
> > View this thread: http://www.excelforum.com/showthread...hreadid=535129
> >

[davegb]

Kletcho wrote:
> You could look at using Excel's built in form (data -- forms).

Sounds like that might have possibilities. Hadn't thought of using a
form to input the new data. But I'd still have to have code to
unprotect/protect the document to enter the data, wouldn't I?

Still, it might solve other problems. Will think about that approach.

[Don Wiss]

On 21 Apr 2006 14:25:18 -0700, davegb <[email protected]> wrote:

>I know that if I protect a spreadsheet, new rows can't be added. I'm
>building a new spreadsheet for some of our less-knowledgeable users. I
>only want them to be able to enter data, but this means they have to be
>able to add rows for new/additional data. My plan is to create a macro
>that prompts them to select the line above which they want to insert
>new rows, tell it how many rows they want to add (1-10), then unprotect
>the worksheet, add the new rows, adjust the sums to include the new
>rows, if neccessary, then reprotect the spreadsheet.
>
>I'm interested in feedback on:
>
>Is this the best way to do this, or is there a better way to allow them
>access but protect the cells with text and formulas?

I do that all the time. I have several universal macros to do this. One
calls an InputBox telling the user how many rows the range already has, and
asking for how many to add/delete. Then AddRowsCore takes the range name,
the number of rows to add, plus pairs of column letters. First pair is the
first and last column, then other pairs are column starts and stops for
moving up the user's data. You have to insert one row above the last to
keep the ranges intact. They could have data in the last row. So you move
it up. Other columns are copied down. Then the third macro deletes rows if
they enter a negative number. And a button from the Forms toolbar starts it
all.

Don <www.donwiss.com> (e-mail link at home page bottom).

[davegb]

Don Wiss wrote:
> On 21 Apr 2006 14:25:18 -0700, davegb <[email protected]> wrote:
>
> >I know that if I protect a spreadsheet, new rows can't be added. I'm
> >building a new spreadsheet for some of our less-knowledgeable users. I
> >only want them to be able to enter data, but this means they have to be
> >able to add rows for new/additional data. My plan is to create a macro
> >that prompts them to select the line above which they want to insert
> >new rows, tell it how many rows they want to add (1-10), then unprotect
> >the worksheet, add the new rows, adjust the sums to include the new
> >rows, if neccessary, then reprotect the spreadsheet.
> >
> >I'm interested in feedback on:
> >
> >Is this the best way to do this, or is there a better way to allow them
> >access but protect the cells with text and formulas?
>
> I do that all the time. I have several universal macros to do this. One
> calls an InputBox telling the user how many rows the range already has, and
> asking for how many to add/delete. Then AddRowsCore takes the range name,
> the number of rows to add, plus pairs of column letters. First pair is the
> first and last column, then other pairs are column starts and stops for
> moving up the user's data. You have to insert one row above the last to
> keep the ranges intact. They could have data in the last row. So you move
> it up. Other columns are copied down. Then the third macro deletes rows if
> they enter a negative number. And a button from the Forms toolbar starts it
> all.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

Thanks for the replies. After looking over the various possible
approaches, I decided it would probably be easier to just have the
macro create a new worksheet from scratch each time. This seemed easier
than adding/deleting rows when there are subtotals between the
different categories which would have to be adjusted each time. Seemed
like a lot of possibilities for problems with the macro. It will use
some of the ideas all of you submitted.