Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding right click menu item to insert a blank row
Hi,
I often use the row right click menu to add a row. I find that when I have something on my clipboard the "Insert" command is replaced with a "Insert copied cells" I find this a pain as I have to press escape to cancel the copy command, insert the row, go back to where the source is and copy it again. I was hoping to be able to add a menu item to the row right click menu to add an entire row, that way when I have something on my clipboard I can at least insert the row without having to cancel the command and reselect to insert. I have written the following coder to add an "Insert entire row to the row right click menu: Option Explicit Public RtClkRowMenu As CommandBarButton Sub DDeleteSKBRightClickRowMenuControl() Dim i As Long Dim caption_names As Variant caption_names = Array("Insert Entire Row", "caption 2", "caption 3") With Application.CommandBars("Row") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub And the following code to insert an entire row: Sub IInsertEntireRow() Dim strStartCell As String strStartCell = ActiveCell.Address ActiveCell.Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown Range(strStartCell).Select 'Leave cursor in cell that was initially selected End Sub The problem I am having is that when I have something on my clipboard and use my custom right click menu item the contents of the clipboard are pasted into every cell in the row that I have just inserted. Is there a way to get a blank row inserted without the contents of the clipboard being pasted into every cell? Ideally I would like to keep the clipboard contents on the clipboard so that they can be pasted in after the row has been inserted. Any assistance/explanation will be appreciated. Regards. Sean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding right click menu item to insert a blank row
Sean,
The problem is more than what you describe. If you copy a range of rows then it will insert the same number of rows as you copied instead of just one. All the inserted rows will also be populated as you described. I don't know how to control this. The first of the below two macros will add a temporary button ("Insert Row") to the right click popup menu and set its OnAction property to the InsertRow macro. I made it temporary so that it will not persist after Excel is closed. You can use the Workbook_Open event to add it automatically for appropriate workbooks. You can optionally remove the Temporay:=True statement to make it permanent and can delete it programmatically if you want. The second macro works by: 1. Passing the clipboard contents to a variable 2. Clearing the clipboard 3. Inserting a row at the position of the active cell 4. Refilling the clipboard with the saved contents that were passed to the variable 5. Then clicking the Paste button will paste the copied contents to the active cell even though Cut/Copy mode is no longer on ("marching ants" border is no longer active). Note that for the second macro to work you will have to set a reference to the Microsoft Forms 2.0 Object Library through the VBE's toolbar: Tools References. I've never had a need for this myself and so have no experience with it. Just wrote it now (minimal testing). Seems OK. Regards, Greg Sub AddInsertBtn() Dim btn As CommandBarButton With Application.CommandBars("Cell") Set btn = .Controls.Add(Befo=6, Temporary:=True) btn.Caption = "Insert Row" btn.OnAction = "InsertRow" End With End Sub Private Sub InsertRow() Dim DataObj As DataObject Set DataObj = New DataObject Dim clipdata As String DataObj.GetFromClipboard clipdata = DataObj.GetText Application.CutCopyMode = False ActiveCell.EntireRow.Insert DataObj.SetText clipdata DataObj.PutInClipboard End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding right click menu item to insert a blank row
Thanks Greg,
I appreciate the info, will try your suggestion Sean "Greg Wilson" wrote in message ... Sean, The problem is more than what you describe. If you copy a range of rows then it will insert the same number of rows as you copied instead of just one. All the inserted rows will also be populated as you described. I don't know how to control this. The first of the below two macros will add a temporary button ("Insert Row") to the right click popup menu and set its OnAction property to the InsertRow macro. I made it temporary so that it will not persist after Excel is closed. You can use the Workbook_Open event to add it automatically for appropriate workbooks. You can optionally remove the Temporay:=True statement to make it permanent and can delete it programmatically if you want. The second macro works by: 1. Passing the clipboard contents to a variable 2. Clearing the clipboard 3. Inserting a row at the position of the active cell 4. Refilling the clipboard with the saved contents that were passed to the variable 5. Then clicking the Paste button will paste the copied contents to the active cell even though Cut/Copy mode is no longer on ("marching ants" border is no longer active). Note that for the second macro to work you will have to set a reference to the Microsoft Forms 2.0 Object Library through the VBE's toolbar: Tools References. I've never had a need for this myself and so have no experience with it. Just wrote it now (minimal testing). Seems OK. Regards, Greg Sub AddInsertBtn() Dim btn As CommandBarButton With Application.CommandBars("Cell") Set btn = .Controls.Add(Befo=6, Temporary:=True) btn.Caption = "Insert Row" btn.OnAction = "InsertRow" End With End Sub Private Sub InsertRow() Dim DataObj As DataObject Set DataObj = New DataObject Dim clipdata As String DataObj.GetFromClipboard clipdata = DataObj.GetText Application.CutCopyMode = False ActiveCell.EntireRow.Insert DataObj.SetText clipdata DataObj.PutInClipboard End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add menu item to right click | Excel Programming | |||
Adding a item to the right click menu? | Excel Programming | |||
Adding Sub Menu Item to Current Custom Menu | Excel Programming | |||
Add item to sheet right-click menu? | Excel Programming | |||
Adding a menu item right click menu when clicking on a single. | Excel Programming |