Adding right click menu item to insert a blank row

[Sean]

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

 

[Guest]

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(Before:=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

 

[Sean]

Thanks Greg,

I appreciate the info, will try your suggestion

Sean