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.

If you have Excel 2003, you can leave a row unlocked wherever you wan
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 unlocke
cells", "Insert Rows". I don't think excel has this function in earlie
versions

--
renega
-----------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...fo&userid=1045
View this thread: http://www.excelforum.com/showthread.php?threadid=53512

Hi renegan

Excel 2002 is the first version with this option

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


"renegan" wrote in message
...

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

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

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" wrote in message
...

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 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.

On 21 Apr 2006 14:25:18 -0700, davegb 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).

Don Wiss wrote:
On 21 Apr 2006 14:25:18 -0700, davegb 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.