SendKeys to insert new row

[TheJay]

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?

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.

 

[TheJay]

If anyone else is interested, this is the code that works (and removes focus from highlighting the whole of the newly inserted row).

Sub InsertRow()
Application.SendKeys "+( )%(ir){UP}{DOWN}"
End Sub

It doesn't appear that it is possible to bind to Ctrl + I though.

 

[cmowla]

Sendkeys is unstable. (It doesn't work consistently.) Here's a stable alternative that I would advise you to consider (which I rarely do).

Look at: Application.OnKey method (Excel)

For your specific case, you would need to have a sub which assigns the key press, but another sub which UNASSIGNS it. Because it will (if I recall correctly) carry to other workbooks if they are opened at the same time as this one. If not, then that's even better. (But I recall something like that.)

Put these subs in a standard module:

Sub Disable_Key()
Application.OnKey "^i", "Do_Nothing"
End Sub
Sub Do_Nothing()
'Do nothing
End Sub
Sub Insert_Row()
MsgBox "Insert Row Sub is called now", vbInformation, "Successful"
End Sub

And perhaps put this sub in the Sheet's sheet code (right click on the sheet tab and select View Code). It's got to go in an Event sub somewhere. Either here or where I suggest next, perhaps.

Private Sub Worksheet_Activate()
Application.OnKey "^i", "Insert_Row"
End Sub

Or if you want to apply this to the entire Workbook at the start, double click on this and put the following code in there:

Private Sub Workbook_Open()
Application.OnKey "^i", "Insert_Row"
End Sub

And if you want the key to be unassigned when you close this Workbook (so that it's only assigned in this workbook and not others), in the same module you can put the unassign code from above instead of in a standard code module:

Private Sub Workbook_Open()
Application.OnKey "^i", "Insert_Row"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^i", "Do_Nothing"
End Sub

 

[TheJay]

Thank you very much for your reply. It doesn't work quite the same way, does it?

I tried replacing existing VBA and it's coming up with errors. For example:

Sheet3 Code

Sub Worksheet_Activate()
Application.OnKey "^i", "InsertRow"
End Sub

Sub Worksheet_Deactivate()
Application.OnKey "^i", ""     'Empty double quotes remove binding?
End Sub

InsertRow Module

Sub InsertRow()
Application.OnKey "+( )%(ir){UP}{DOWN}"
End Sub

It doesn't like that key combination, which worked with SendKeys.

 

[cmowla]

Application.OnKey "^i", ""

That's incorrect. You literally need to have it call a sub that does nothing. (Just like I had it before.) You have to assign it to a sub. If the sub has no code in it, it will disable the assigned keypress (in effect).

 

[TheJay]

Okay, I'll keep that in mind. It's still reporting a problem with the keypresses changing from sendkeys to onkey?

 

[cmowla]

I don't know how they work together. So you will have to choose one or the other. But I already mentioned why you shouldn't use SendKeys. (And it's not just my opinion. A lot of people have said the same thing.)

 

[TheJay]

I'm happy to use OnKey, as long as it can input the keys as mentioned previously.

 

[TheJay]

According to this page, it doesn't need to refer to a macro?

Excel Macros - OnKey

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Call Application.OnKey(key) - restore hotkey assignment to normal 
Call Application.OnKey(key, "") - disables hotkey

Is that not your interpretation?

 

[TheJay]

Further reading would suggest it's even simpler:

Programming Excel with VBA and .NET

Name Application.OnKey(Key, [Procedure]) Synopsis Assigns a macro to run when a key is pressed. Can also be used to disable built-in Excel key combinations. Argument Setting … - Selection from Programming Excel with VBA and .NET [Book]

www.oreilly.com

The following code demonstrates how to reassign, disable, and restore a built-in key assignment:

Sub TestOnKey(  )
    ' Reassign Ctrl+C
   Application.OnKey "^c", "CopyMsg"
   ' Disable Ctrl+C
   'Application.OnKey "^c", ""
   ' Restore Ctrl+C
   ' Application.OnKey "^c"
End Sub
 
Sub CopyMsg(  )
    MsgBox "You can't copy right now."
End Sub