Format of inserted row

pilot · Feb 28th, 2002, 08:22 AM

Excel 97 - When inserting a row using the right click shortcut, the inserted row adopts formatt characteristics of the row above. Is there a way to make it adopt the format of the row below instead?

pilot · Mar 4th, 2002, 06:54 AM

Wanted to raise this to the top of the stack one time since no takers. Any info appreciated.

Tom Urtis · Mar 4th, 2002, 09:33 AM

See if this helps at all.

Right click on your sheet tab name, left click on View Code, and paste this in:

'''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Resp As Integer
Resp = MsgBox("Do you want to insert a row here?", 68, "Just checking...")
If Resp = 7 Then Exit Sub
Application.ScreenUpdating = False
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.EntireRow.PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
Application.ScreenUpdating = True
Cancel = True
End Sub

''''''''''''''''''''''''''''''''''''

Then, follow the prompts when you right click on that sheet.

HTH

Mar 4th, 2002, 09:50 AM

Thanks, Tom. I knew this was possible through VBA but hadn't gone to the effort of writing the code.

I usually try to accomplish such tasks without macros because much of what I do with other users involves running Excel over a network and the macro warning is enabled by default. I find that most users I work with will not enable macros as soon as they see any verbiage about virus protection.

It would be nice if Excel made this a global option (choosing whether format of row above or below, left or right is used on Inserted row, column).

Mar 4th, 2002, 09:33 AM	#3
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,382	See if this helps at all. Right click on your sheet tab name, left click on View Code, and paste this in: ''''''''''''''''''''''''''''''''''''' Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim Resp As Integer Resp = MsgBox("Do you want to insert a row here?", 68, "Just checking...") If Resp = 7 Then Exit Sub Application.ScreenUpdating = False ActiveCell.EntireRow.Insert ActiveCell.Offset(1, 0).EntireRow.Copy ActiveCell.EntireRow.PasteSpecial (xlPasteFormats) Application.CutCopyMode = False Application.ScreenUpdating = True Cancel = True End Sub '''''''''''''''''''''''''''''''''''' Then, follow the prompts when you right click on that sheet. HTH __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 08:22 AM	#1
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	Excel 97 - When inserting a row using the right click shortcut, the inserted row adopts formatt characteristics of the row above. Is there a way to make it adopt the format of the row below instead?

Mar 4th, 2002, 06:54 AM	#2
pilot Board Regular Join Date: Feb 2002 Location: Houston, TX Posts: 303	Wanted to raise this to the top of the stack one time since no takers. Any info appreciated.

Mar 4th, 2002, 09:50 AM	#4
Guest Posts: n/a	Thanks, Tom. I knew this was possible through VBA but hadn't gone to the effort of writing the code. I usually try to accomplish such tasks without macros because much of what I do with other users involves running Excel over a network and the macro warning is enabled by default. I find that most users I work with will not enable macros as soon as they see any verbiage about virus protection. It would be nice if Excel made this a global option (choosing whether format of row above or below, left or right is used on Inserted row, column).