• Creating a keyboard command?


    I want to know if there is an easy way to create a keyboard command for some of the things I do quite often in Excel.  For example, I would like to have the command <CTRL> R to insert a new row and <CTRL> C to insert a new column.  Can I do this without having to write a VBA program?  Alternatively, can I use (CTRL>I for a row insertion and <ALT> I for a column insertion?

    Any insight that anyone would care to provide will be greatly appreciated.  Thanks.

    Ron M

    • This topic was modified 4 years, 2 months ago by Ron M.
    • This topic was modified 4 years, 2 months ago by Ron M.
    Viewing 9 reply threads
    • #1876568

      I think you have a choice of;

      1. The Quick Access Toolbar
      2. Macros

      How to Make Your Own Excel Keyboard Shortcuts the Easy Way

      Windows 11 Pro version 22H2 build 22621.2361 + Microsoft 365 + Edge

    • #1876762

      @b,thanks for the reference.  It looks like the best way forward would be to record a macro and associate a specific keystroke set with it, as it looks like I cannot do what I would like to do.

    • #1876772

      You can roll your own with AutoIt or AutoHotKey.

      cheers, Paul

    • #1876773

      Thanks Paul.  New apps I haven’t run into before.  I had a quick look at them and, being a non-techie, it wasn’t intuitively obvious to me how they fit into Excel as the sites I looked at seemed to show their use within Windows.  I think I will try recording a Macro within Excel and see if I can make that work for what I want.  That is about as “techie” as I can get and still follow what is happening.

      • This reply was modified 4 years, 2 months ago by Ron M.
    • #1876844

      A couple of other options, Ron:

      1. A programmable keyboard. There are many out there, I’m very happy with the ROCCAT Isku I got 3 years ago for $70—it’s under $50 now.

      2. Instead of the excellent AutoIt or AutoHotKey, I use TypeItIn which is a simple visual app with a lot of power behind the GUI. A major advantage for me is I don’t have to remember any shortcuts, I find that onerous once it gets in the 50+ range. You can still set a KB shortcut key for any command if you wish.

      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

      1 user thanked author for this post.
    • #1877017


      Here’s the code for inserting a column by moving the currently selected column to the left.

      '                         +-------------------------+             +----------+
      '-------------------------|     ColumnInsert()      |-------------| 07/19/19 |
      '                         +-------------------------+             +----------+
      'Arguments: N/A
      Sub ColumnInsert()
         Dim rngActiveCell As Range
         Set rngActiveCell = ActiveCell
         With rngActiveCell
             Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
             .Offset(0, -1).Select 'Set selection to the initial row in the inserted column.
                                   'Remove Offset to keep selection in the old location.
         End With
      End Sub 'ColumnInsert
      Note: the comments in the code about where the cursor will land after the insert.
      Place the code in a module of your Personal.xlsx file then use Alt+F8 to find the macro and use the Options button to assign your desired hot key combo (I chose Ctrl+Shift+I). By doing this it will be available to ALL of your Excel Workbooks.
      HTH :cool:

      May the Forces of good computing be with you!


      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
      • #1877553

        Hi RG

        OK, I’ve tried and tried but still don’t remember how to use the right tags for ‘wrapping code’ in these posts. You know how to do it. Tell me your secret. Again. Please.

        I’ve had amnesia for as long as I can remember

    • #1877021

      Thanks RG.  Looks interesting AND I can actually understand the code…well, pretty well 🙂 .   It has been many, many, many years since I have had to code anything and that was some Fortran (told it was a long time ago), some very limited C coding and some early Basic stuff.  Now, if I can figure out how to do it for a row insertion.


      • #1877544

        Hi Ron

        You can assign shortcut keys to any macros you create.
        Your shortcut key must be a letter, and is used with the [Ctrl key.
        It is best to avoid using any existing Excel shortcut keys, so best not to use [Ctrl][C] for inserting columns, as that shortcut is already used for “Copy”.
        And best not to use [Ctrl][R] for inserting rows as that is already used in Excel for “fill right”.

        The good news is that you can use [Ctrl][Shift][letter] for your shortcuts, and most of the letters are available to use without impacting existing shortcuts. I like using [Ctrl[Shift][Z] of course.

        So this means we can use [Ctrl][Shift][C] to insert Column(s), and we can use [Ctrl][Shift][R] for inserting rows, as in:

        ‘using shortcut [Ctrl][Shift][C]
        Sub insertColumn()
        End Sub
        ‘using shortcut [Ctrl][Shift][R]
        Sub insertRow()
        End Sub

        I have attached a file with this vba code in.

        We can tell you how to assign shortcut keys to your macros if we know what Excel version you are using, e.g. Excel2010, Excel2013, Excel2016, Excel2019 etc etc etc

        Excel Battle Angel Assistant


        • This reply was modified 4 years, 2 months ago by zeddy.
        • This reply was modified 4 years, 2 months ago by zeddy.
        • This reply was modified 4 years, 2 months ago by PKCano.
        2 users thanked author for this post.
    • #1877672

      zeddy, I am using Excel 365.


      • #1880172

        Hi Ron

        1. Open the workbook you want to use with the new shortcut keys e.g. fred.xlsx
        2. When the workbook has loaded, press [Alt][F11] to open the Visual Basic Editor (VBE)
        3. Press [Ctrl][R] to display the VBA Project Explorer in the left-pane of the Visual Basic Editor (if the left pane is not already showing). Pressing [Ctrl][R] doesn’t hurt if the left pane is already visible.
        4. in the top-left corner of this pane you should see your workbook name in brackets e.g. VBAProject(fred.xlsx), so Right-click on your workbook name and select Insert -> Module from the context menu.

        5. Copy the VBA code and paste it into the right-hand pane of the VBE (“Module1” window)
        6. Press [Alt][F11] to return to the Excel worksheet
        7. After adding the macros via the Visual Basic Editor you now need to save the workbook as a macro-enabled filetype e.g. as Fred.xlsm
        8. After saving the file, press [Alt][F8] to show the list of macros..

        9. click on the insertColumn macro in the list of macros to select it, then click on the [Options…] button to display the Macro Options dialog.
        10. The Shortcut key will show a blinking cursor as it waits for you to type the letter key you want to use for the shortcut you are going to assign to the highlighted macro.
        11. Enter C (i.e. as a Capital letter C) and it will then show as Ctrl + Shift + [C]
        12. Click the [OK] button to complete this shortcut key assignment.
        13. Now highlight the second macro in the list insertRow, click the [Options..] button, and then enter R (i.e. as a Capital R) for the second shortcut key assignment
        14. Now click the [Cancel] button to hide the Macro Options dialog
        15. Now save the workbook again.
        16. Close the workbook, then re-open the workbook
        17. Above the formula bar you will see a ! SECURITY WARNING Macros have been disabled. [Enable Content]
        18. Click the [Enable Content] button to enable your macros.

        You should then be able to test your shortcut key assignements.

        Excel Visual Effects Head

    • #1881523

      zeddy, thank you very much for the detailed instructions.  I will be leaving on holidays for a couple of weeks and with all the packing and organizing clothes and photo gear, etc., I won’t have a chance to get to this until I am back.  I will let you know then, how things went.

      • #1881528

        Hi Ron

        Never mind the Excel stuff, concentrate on a clean getaway.
        Recharge your batteries and have a great vacation.
        Are you going to the Florida Shortcut Keys???

        Excel Cell Borders Guard

    • #1882608

      I am headed for the Masai Mara in Kenya for two weeks of animal, and other things, photography.  Hoping to catch the great migration.

      1 user thanked author for this post.
      • #1883653

        Hi Ron

        Thank you for sharing your gnu’s.
        If they stampede towards you, look for a shortcut.
        Much better than a Windows migration in my opinion.
        And so many zebras!
        (I love anything with z’s in)

        Excel Ranger With Tripod

        • #1888407

          Hi Ron

          It is actually possible to create shortcut keys in Excel for inserting rows and inserting columns without using any macros. You can do this because Excel already has these commands available in the Top-Panel Ribbon.

          To make them available via keyboard-shortcut rather than mouse-clicks, you just need to add these commands to a Quick Access Toolbar (QAT). Once they are in your QAT, you can type [Alt][1] to execute your first QAT item, [Alt][2] to execute your 2nd QAT item, [Alt][9] to execute your 9th item etc etc etc.

          To see what you need to type for each item in your QAT, just press the [Alt] key to see the corresponding keys.

          If you follow the link in the first reply to your post, it will tell you how to customize the Quick Access Toolbar (it applies to your Excel in Office365).

          So, if you put the Insert-Columns command as your first item in your QAT, you could then just use [Alt][1] to insert columns. Putting the Insert-Rows command as your second QAT item lets you use [Alt][2] etc etc etc.

          The QAT is a very useful feature and you should definitely add your favourite commands to it.
          You can Export/Import QAT settings, which allows you to transfer your favourites to another computer. These are stored in files with a file extension of .exportedUI but unfortunately I cannot upload a zeddy.exportUI file to this forum.

          Excel Disruptive Innovation Team

          1 user thanked author for this post.
    Viewing 9 reply threads
    Reply To: Creating a keyboard command?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: