News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Creating a keyboard command?

    Posted on Ron M 43 Comment on the AskWoody Lounge

    This topic contains 19 replies, has 8 voices, and was last updated by  zeddy 3 weeks, 6 days ago.

    • Author
      Posts
    • #1876547 Reply

      Ron M 43
      AskWoody Lounger

      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 1 month ago by  Ron M 43.
      • This topic was modified 1 month ago by  Ron M 43.
    • #1876568 Reply

      b
      AskWoody Plus

      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

      Knuckle dragger Cannon fodder Chump Daft glutton Idiot Crazy/Ignorant Toxic drinker Blockhead Unwashed mass Seeker/Sucker "Ancient/Obsolete" (Group ASAP) Win10 v.1903

    • #1876762 Reply

      Ron M 43
      AskWoody Lounger

      @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 Reply

      Paul T
      AskWoody MVP

      You can roll your own with AutoIt or AutoHotKey.

      cheers, Paul

    • #1876773 Reply

      Ron M 43
      AskWoody Lounger

      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 1 month ago by  Ron M 43.
    • #1876844 Reply

      Lugh
      AskWoody_MVP

      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.

      Lugh.
      ~
      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 Reply

      RetiredGeek
      AskWoody MVP

      Ron,

      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
             .EntireColumn.Select
             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!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
      • #1877553 Reply

        zeddy
        AskWoody_MVP

        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.

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

    • #1877021 Reply

      Ron M 43
      AskWoody Lounger

      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.

      Ron

      • #1877544 Reply

        zeddy
        AskWoody_MVP

        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()
        Selection.EntireColumn.Insert
        End Sub
        
        ‘using shortcut [Ctrl][Shift][R]
        Sub insertRow()
        Selection.EntireRow.Insert
        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

        zeddy
        Excel Battle Angel Assistant

        zeddy-insert-rows-or-columns-1a-1

        • This reply was modified 1 month ago by  zeddy.
        • This reply was modified 1 month ago by  zeddy.
        • This reply was modified 1 month ago by  PKCano.
        Attachments:
        2 users thanked author for this post.
        • #1877571 Reply

          PKCano
          Da Boss

          I fixed this one for you.

          1 user thanked author for this post.
          • #1877578 Reply

            zeddy
            AskWoody_MVP

            Thank you.
            You are my new best friend.

            zeddy
            Excel Standby Safety Crew

    • #1877672 Reply

      anonymous

      zeddy, I am using Excel 365.

      Ron

      • #1880172 Reply

        zeddy
        AskWoody_MVP

        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.
        zeddy1

        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..
        zeddy2

        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.

        zeddy
        Excel Visual Effects Head

        Attachments:
    • #1881523 Reply

      Ron M 43
      AskWoody Lounger

      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 Reply

        zeddy
        AskWoody_MVP

        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???

        zeddy
        Excel Cell Borders Guard

    • #1882608 Reply

      Ron M 43
      AskWoody Lounger

      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 Reply

        zeddy
        AskWoody_MVP

        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)

        zeddy
        Excel Ranger With Tripod

        • #1888407 Reply

          zeddy
          AskWoody_MVP

          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.

          zeddy
          Excel Disruptive Innovation Team

          1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Creating a keyboard command?

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