Can I set Insert Options programmatically?

[Brian Knittel]

Is there a way to set the row Insert Options programmatically in Excel 2003?
I haven't been able to find any object that encompasses this.

What I want is to to be able to have a macro insert a row using the "Format
Same As Above" option, but I don't want the user to have to set this
manually or have to live with the option set if they don't want to. What I
want to do is save the original setting, change it to Format Same As Above,
insert the row, then restore the original setting.

Of course I can copy the various properties from the cells in the row above,
but it's not as nice as having the row just pop into place ready to go.

Any advice would be greatly appreciated.

Brian

 

[Office_Novice]

Try somthing like
'Everytime the cell changes this will insert a new formated row
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormats
End Sub

 

[Brian Knittel]

Interesting, on my copy of Excel and on the msdn online documentation, the
help page doesn't really describe the CopyOrigin argument, and entirely
omits mention of the Shift argument.
(http://msdn.microsoft.com/en-us/library/aa195769(office.11).aspx)

That aside, Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormats doesn't do
the same thing as a row insert with "Format Same As Above" selected. This
code inserts a row above the specified row, using that row's formatting. In
other words, the flow of formatting is still "upwards."

What I want is to insert a row *below* the specified row, using that row's
formatting. In other words I start with this

+---------+
| AAA |
+---------+
| BBB |
+---------+

and want end up with this:

+---------+
| AAA |
+---------+
| aaa | <-- this is the new row
+---------+
| BBB |
+---------+

I want the names defined in row AAA its formulas and values to stay exactly
as they are.
I just want row aaa to have the same formatting as AAA (borders,
interior,protection, cell format, etc).

Using Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormats I get this

+---------+
| aaa | <-- this is the new row
+---------+
| AAA |
+---------+
| BBB |
+---------+

Any other ideas on how to programmatically control the Format Same As Above
setting?

Brian

 

[Brian Knittel]

ummm, forget I said this: