Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I set Insert Options programmatically?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I set Insert Options programmatically?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I set Insert Options programmatically?
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/libr...ffice.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 "Office_Novice" wrote... Try somthing like Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormats |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I set Insert Options programmatically?
ummm, forget I said this:
...and entirely omits mention of the Shift argument. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically insert a graphic? | Excel Programming | |||
Programmatically disable new smart options | Excel Programming | |||
how do i programmatically insert a button on a worksheet | Excel Programming | |||
Setting Printer Options Programmatically | Excel Programming | |||
Who can tell me how to programmatically insert an image into Excel by C#? | Excel Programming |