SendKeys to insert new row

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
Hello there,

I want to trigger inserting a new row using Ctrl + I by assigning that combination as a macro bind.

Can someone please tell me what's wrong with my code so that it works?

VBA Code:
Sub InsertRow()
Application.SendKeys "(+"" "")"    'Shift+Spacebar to select the row. Space (" ")
Application.SendKeys "(%ir)"       'Alt+I+R to add a new row above.
End Sub

'For SHIFT use +
'For CTRL use ^
'For ALT use %

Thanks.
 
This also seems to confirm:


In theory this should work:

VBA Code:
Public Sub Worksheet_Activate()
Application.OnKey "^ ", "+( )%(ir){UP}{DOWN}"
End Sub

Public Sub Worksheet_Deactivate()
Application.OnKey "^ "
End Sub
 
Last edited:
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
The problem I am experiencing is:

9OQCBOy.png


Could you please check out my sample workbook and see if you get the same problem when pressing "CTRL" and "SPACE"?


nc9dt7q.png


Thanks.
 
Upvote 0
All of those keys are what you were using for SendKeys. If all you want to do is Ctrl I for row insert, I already told you all you need to know in this post: SendKeys to insert new row It works for me now (with Office 365) and worked for me when I had Office 2016. If it doesn't work for you (the way I wrote it, exactly), I hope someone else will come and help you.
 
Upvote 0
I don't know why, it only works if I add the code to the sheet and specifically refer to it? If I don't, I get that macro error. Surely I should be able to add it to ThisWorkbook or a module and it still work?

VBA Code:
Private Sub Worksheet_Activate()
Application.OnKey "^ ", "Sheet1.Row"
End Sub

Public Sub Row()
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "^ "
End Sub

Sub Fill()
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Trend:=True
End Sub

I realised I could do this without SendKeys.

VBA Code:
Public Sub Row()
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

This works perfectly.
 
Upvote 0
After a lot of frustration, I have figured out that you have to be very specific about what you refer to.

For example, if referring to a module, it needs to be "modulename.modulesubname". For me, that's "Row.Row". You may have not changed the module properties and only defined the sub name. That would be "Module1.Row".

ThisWorkbook
VBA Code:
Private Sub Workbook_Open()
    ' Bug in Excel:
    ' The Worksheet_Activate event does not fire for the sheet that is active
    ' when the workbook is opened, so call it explicitely. Make sure that
    ' Worksheet_Activate() is declared as Public.
    ' Ignore ActiveSheets without (Public) Worksheet_Activate()
    On Error Resume Next
    Call ActiveSheet.Worksheet_Activate
    On Error GoTo 0
'Application.OnKey "^ ", "Row"
End Sub

Sheet1
VBA Code:
Public Sub Worksheet_Activate()
Application.OnKey "^ ", "Row.Row"
End Sub

Public Sub Row()
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "^ "
End Sub

Sub Fill()
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Trend:=True
End Sub

Row Module
VBA Code:
Public Sub Row()
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top