Inserting rows from a command button
 

Hi all

I'd like users to be able to insert rows into my protected workshee
witghout having to unprotect it first.

Therefore I've got a command button on the sheet and I'd like it t
when clicked:

1. unprotect the sheet using my password "jon"
2. insert rows for me (I've already asked the user to select the row
he's like to have inserted, i.e. selected using the row numbers on th
left of the screen. For example if they want to insert a copy of row
through 7 below row 7 then they have selected rows 4-7 and they hav
turned black)
3. protect the sheet again using my password "jon"

I've got the following so far, but don't know what to put in the middl
to make the INSERT ROWS bit happen.

CommandButton1.TakeFocusOnClick = False
ActiveSheet.Unprotect Password:="jon"
--WHAT DO I PUT IN HERE??--
ActiveSheet.Protect Password:="jon"

Any ideas?? Many thanks in advance for your help

Jo

--
Message posted from http://www.ExcelForum.com

Inserting rows from a command button
 

Selection.EntireRow.Insert

--
Regards,
Tom Ogilvy

"jontait " wrote in message
...
Hi all

I'd like users to be able to insert rows into my protected worksheet
witghout having to unprotect it first.

Therefore I've got a command button on the sheet and I'd like it to
when clicked:

1. unprotect the sheet using my password "jon"
2. insert rows for me (I've already asked the user to select the rows
he's like to have inserted, i.e. selected using the row numbers on the
left of the screen. For example if they want to insert a copy of row 4
through 7 below row 7 then they have selected rows 4-7 and they have
turned black)
3. protect the sheet again using my password "jon"

I've got the following so far, but don't know what to put in the middle
to make the INSERT ROWS bit happen.

CommandButton1.TakeFocusOnClick = False
ActiveSheet.Unprotect Password:="jon"
--WHAT DO I PUT IN HERE??--
ActiveSheet.Protect Password:="jon"

Any ideas?? Many thanks in advance for your help

Jon


---
Message posted from http://www.ExcelForum.com/

Inserting rows from a command button
 

If you want to copy the selected data and not just insert....
(as looks from your update...)

selection.Copy Destination:= selection.Offset(selection.rows.count,0)

This should help

jontait < wrote:

Hi all

I'd like users to be able to insert rows into my protected worksheet
witghout having to unprotect it first.

Therefore I've got a command button on the sheet and I'd like it to
when clicked:

1. unprotect the sheet using my password "jon"
2. insert rows for me (I've already asked the user to select the rows
he's like to have inserted, i.e. selected using the row numbers on the
left of the screen. For example if they want to insert a copy of row 4
through 7 below row 7 then they have selected rows 4-7 and they have
turned black)
3. protect the sheet again using my password "jon"

I've got the following so far, but don't know what to put in the middle
to make the INSERT ROWS bit happen.

CommandButton1.TakeFocusOnClick = False
ActiveSheet.Unprotect Password:="jon"
--WHAT DO I PUT IN HERE??--
ActiveSheet.Protect Password:="jon"

Any ideas?? Many thanks in advance for your help

Jon


---
Message posted from http://www.ExcelForum.com/

Inserting rows from a command button
 

Thanks yogendra

What I'd like to be able to do, is where the user wants to insert a ro
between a series of rows, they select the row they want, then the
click a button and then that inserts a copy of that row below the on
they have clicked.

This copy would contain the same formatting, font size and colour et
and would contain the same formulas, but these formulas would hav
changed their cell references to reflect the new rows they are in.

Almost as if you'd simply grabbed the selected row and "copied down"


This code will do the "insert rows bit below" but doesn't copy all th
formatting and relative formauls I need it to

ActiveSheet.Unprotect Password:="xxxxx"
Selection.EntireRow.Insert shift:=xlDown
ActiveSheet.Protect Password:="xxxxx"

ANy ideas

--
Message posted from http://www.ExcelForum.com