| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 2 formatted rows at cursor location
It must be Friday. Drawing blank!?
Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
Hi,
This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
It would help had I posted the code
ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
Hi Mike, it should be simple enough to copy two rows that are formatted and
then use your code to insert them. myFormattedRows.Copy myDestinationRange.Activate ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select "Mike H" wrote: It would help had I posted the code ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
I think your right I should have realised it was formats copied from
exisiting rows "JLGWhiz" wrote: Hi Mike, it should be simple enough to copy two rows that are formatted and then use your code to insert them. myFormattedRows.Copy myDestinationRange.Activate ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select "Mike H" wrote: It would help had I posted the code ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
Gentlemen:
Thanks so much. You're both correct in that I likely should use a "copy" of existing row(s). However, due to the frequency of use, over quite a number of wb's, I find it convenient to attach this type of procedure to an icon on one of my custom toolbars. It gives me flexibility in use. If a change is required, I change ONE location and then access that change, when required, by clicking on my icon. Yes, I know that I can set up a "master" sheet with the formatted rows and copy from there by a click of the icon (now that you've shown me the insert steps). However, this best follows current practice AND lets me learn some more new things. What I'm trying to accomplish: 'new row 1 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 19.5 ' New Row 2 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 6.0 ' Columns B thru G With Selection.Interior .ColorIndex = 1 .Pattern = xlSolid End With Thanks for your support and help. I see both of you frequently as I browse thru the newsgroups. I hope you ( and all the other able participants ) enjoy this as much as the "receivers" benefit and learn from it. "Mike H" wrote: I think your right I should have realised it was formats copied from exisiting rows "JLGWhiz" wrote: Hi Mike, it should be simple enough to copy two rows that are formatted and then use your code to insert them. myFormattedRows.Copy myDestinationRange.Activate ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select "Mike H" wrote: It would help had I posted the code ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
One way to do that would be to create subroutines that could be called like
functions to set the formats of a range, The code below would set the format for the range specified in the calling sub. Sub Fmt() Call myFormat(Range("C2:E2")) End Sub Sub myFormat(myRange As Range) With myRange With .Cells(myRange.Row - 1, 1).Font .Name = "Arial" .Size = 12 End With .Cells(myRange.Row - 1, 2).Interior.ColorIndex = 5 .Cells(myRange.Row - 1, 3).NumberFormat = "$##,##0.00" End With End Sub "BEEJAY" wrote: Gentlemen: Thanks so much. You're both correct in that I likely should use a "copy" of existing row(s). However, due to the frequency of use, over quite a number of wb's, I find it convenient to attach this type of procedure to an icon on one of my custom toolbars. It gives me flexibility in use. If a change is required, I change ONE location and then access that change, when required, by clicking on my icon. Yes, I know that I can set up a "master" sheet with the formatted rows and copy from there by a click of the icon (now that you've shown me the insert steps). However, this best follows current practice AND lets me learn some more new things. What I'm trying to accomplish: 'new row 1 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 19.5 ' New Row 2 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 6.0 ' Columns B thru G With Selection.Interior .ColorIndex = 1 .Pattern = xlSolid End With Thanks for your support and help. I see both of you frequently as I browse thru the newsgroups. I hope you ( and all the other able participants ) enjoy this as much as the "receivers" benefit and learn from it. "Mike H" wrote: I think your right I should have realised it was formats copied from exisiting rows "JLGWhiz" wrote: Hi Mike, it should be simple enough to copy two rows that are formatted and then use your code to insert them. myFormattedRows.Copy myDestinationRange.Activate ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select "Mike H" wrote: It would help had I posted the code ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert 2 formatted rows at cursor location
Greetings all.
Thanks much for the input. As often is the case, timely tip(s) for a specific situation provides solutions for other projects in the system. Kinda funny how that works. "JLGWhiz" wrote: One way to do that would be to create subroutines that could be called like functions to set the formats of a range, The code below would set the format for the range specified in the calling sub. Sub Fmt() Call myFormat(Range("C2:E2")) End Sub Sub myFormat(myRange As Range) With myRange With .Cells(myRange.Row - 1, 1).Font .Name = "Arial" .Size = 12 End With .Cells(myRange.Row - 1, 2).Interior.ColorIndex = 5 .Cells(myRange.Row - 1, 3).NumberFormat = "$##,##0.00" End With End Sub "BEEJAY" wrote: Gentlemen: Thanks so much. You're both correct in that I likely should use a "copy" of existing row(s). However, due to the frequency of use, over quite a number of wb's, I find it convenient to attach this type of procedure to an icon on one of my custom toolbars. It gives me flexibility in use. If a change is required, I change ONE location and then access that change, when required, by clicking on my icon. Yes, I know that I can set up a "master" sheet with the formatted rows and copy from there by a click of the icon (now that you've shown me the insert steps). However, this best follows current practice AND lets me learn some more new things. What I'm trying to accomplish: 'new row 1 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 19.5 ' New Row 2 ' cell A insert "^^" ' Cell B Insert "x" ' Row Height = 6.0 ' Columns B thru G With Selection.Interior .ColorIndex = 1 .Pattern = xlSolid End With Thanks for your support and help. I see both of you frequently as I browse thru the newsgroups. I hope you ( and all the other able participants ) enjoy this as much as the "receivers" benefit and learn from it. "Mike H" wrote: I think your right I should have realised it was formats copied from exisiting rows "JLGWhiz" wrote: Hi Mike, it should be simple enough to copy two rows that are formatted and then use your code to insert them. myFormattedRows.Copy myDestinationRange.Activate ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select "Mike H" wrote: It would help had I posted the code ActiveCell.Resize(2).EntireRow.Insert Shift:=xlDown ActiveCell.Resize(2).EntireRow.Select Mike "Mike H" wrote: Hi, This will insert 2 rows at the selected cell and leave the 2 new rows selected. I don't understand what formatting your trying to apply. Mike "BEEJAY" wrote: It must be Friday. Drawing blank!? Trying to set up code so that where-ever my cursor is positioned, I can insert 2 formatted rows. Since the rows can be required "anywhere", the formatting needs to reflect the row positioning of the new lines. If someone would kindly help me get started................... What I have now is: Rows("65522:65523").Select Selection.Insert Shift:=xlDown Range("A65522").Select ActiveCell.FormulaR1C1 = "^^" Range("A65523").Select ActiveCell.FormulaR1C1 = "^^" etc.............................. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine cursor location? | Excel Discussion (Misc queries) | |||
| Tracking cursor location | Excel Worksheet Functions | |||
| Function for cursor location | Excel Worksheet Functions | |||
| Cursor Location | Excel Discussion (Misc queries) | |||
| Cursor Location | Excel Programming | |||
Linear Mode
