Format of inserted row

[pilot]

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]

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

 

[Tom Urtis]

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

 

[Guest]

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