Inserting a shift key in Excel

This tutorial shows how to insert a shift key in Excel.

In Excel, you can insert a Shift key that can execute a macro stored in the spreadsheet. A Shift key is literally an on/off key: when you click it, it is pushed in, and when you click it again, it pops back out.

Ribbon for developers

To insert a toggle key, you have to click on the Developer tab in the Ribbon. The Controls group on the Developer tab is used to insert and customize various objects, including toggle buttons. A toggle button requires a macro to create the button’s functionality; everything related to macros is also on the Developer tab.

The Developer tab is not displayed by default in Excel; you may need to enable it. If it is already enabled, jump to Insert Shift Key.

Enable the Developer ribbon

In the Excel bar, go to File > Options.

1. In the panel on the left, click Customize Ribbon.
2. Then check the Developer checkbox in the right column. It must be checked for the Developer tab to appear in the Excel ribbon bar.
3. Finally, click OK to return to Excel.

You can now access the Developer tab from the main ribbon.

Insert shift key

1. In the Ribbon, go to Developer > Controls > Insert > ActiveX Controls, and then click the Shift Key control (see the figure below).

2. Click and drag in the table to set the size and position, and then release the mouse to create the button.

3. Your toggle button is surrounded by handles. You can use the handles to resize the button and indicate that you are in draft mode.
4. On the Developer tab, notice that the Design Mode button is pressed. (Click it to exit Design Mode; the handles around the button disappear.

5. In Design mode and with Shift selected, examine the available properties. In the Ribbon, go to Developer > Controls > Properties.

6. In the Properties window, customize the appearance and behavior of the Shift key.

Some common properties that can be customized are:

• Name: enter a suitable name for the Shift key that describes its function.
• Label: Change the label of the Shift button. For example, if you want the button to show and hide columns, you can change the label to Hide Columns when the columns are displayed and Show Columns when they are hidden.
• ForeColor: Change the color of the button from the default gray.
• Height and Width: View and adjust the size of your button (as an alternative to dragging the button handles).
• LinkedCell: Enable or disable the option to link the button to a cell. If you do this, when you press the button, the linked cell will be filled with TRUE. When you click the button again, the value of the cell is changed to FALSE.
• TextAlignment: By default, the label is in the center of the button, but you can change the text alignment to your liking.

Shift key label

There are two ways to set a label for the Shift key: with the Label property and with VBA code.

1. Enter a new label for the Shift key in the Properties window. This changes the label from, for example, ToggleButton1 to the label you entered in the Label property.
   Since this is a toggle button, it is probably helpful if the label changes when it is clicked. This can be achieved with a macro.

2. In design mode, right-click the button, and thenclick Show Code.

This will take you to the Visual Basic Editor (VBE) and create the click event behind your shift key.

3. Enter the code:

Private Sub btnToggleColumns_Click()
   If btnToggleColumns = True Then
      btnToggleColumns.Caption = "Show Columns"
   Else
      btnToggleColumns.Caption = "Hide Columns"
   End If
End Sub

4. Close the VBE to return to the spreadsheet and exit draft mode. In the Ribbon, go to Developer > Controls > Draft Mode to turn off Draft Mode.

5. Click the Shift key to display the right label from step 3. For example: Show columns when the button is on. Click again to switch back to Hide Columns.

Add functionality to the Shift key

To add the actual function to the Shift key, go back to the VBA code of the macro.

1. In the Ribbon, go to Developer > Controls > Design Mode to re-enable design mode.
2. Right-click the Shift key again, and thenclick Show Code.
3. Edit the click event: Add code to show and hide the columns.

Private Sub btnToggleColumns_Click()
  If btnToggleColumns = True Then
      btnToggleColumns.Caption = "Show Columns"
      Columns("D:E").EntireColumn.Hidden = True
  Else
      btnToggleColumns.Caption = "Hide Columns"
      Columns("D:E").EntireColumn.Hidden = False
End If
End Sub

4. Close the VBE and return to Excel.

If you now click on the “Toggle” button, the label should change and columns D and E should either be shown or hidden depending on the value(True or False) of the button.