Skip to content

Creating Buttons & Shortcuts

Why Use Buttons?

Keyboard shortcuts are great for you, but what about your colleagues who don't know macros exist? Buttons make macros accessible to everyone!

Benefits:

  • User-friendly - Anyone can click a button
  • Visual - Clear indication of what each macro does
  • Professional - Clean interface for reports and shared files
  • Organized - Group related macros together

Creating Your First Button

Let's create a button that runs your FormatTensileTable macro.

Step-by-Step

  1. Go to the Developer tab

  2. Click Insert → Choose Button (first option under Form Controls)

![Button is the first icon in Form Controls section]

  1. Your cursor changes to a crosshair (+)

  2. Click and drag on your worksheet to draw the button (anywhere is fine for now)

  3. The "Assign Macro" dialogue appears automatically

  4. Select FormatTensileTable from the list

  5. Click OK

  6. The button is created with default text "Button 1"

Customize the Button Text

  1. Right-click the button
  2. Select Edit Text
  3. Delete "Button 1" and type: Format Table
  4. Click outside the button to finish

Test It!

Click your button - it should run the macro instantly!

Button Formatting

Make your buttons look professional:

Change Appearance

  1. Right-click the button → Format Control
  2. Font tab:
  3. Font: Segoe UI or Arial
  4. Size: 10-11pt
  5. Style: Regular (bold looks harsh on buttons)
  6. Click OK

Resize and Position

Resize:

  • Click the button to select it
  • Drag the corner handles to resize
  • Hold Shift while dragging to maintain proportions

Position:

  • Drag the button to move it
  • Use Excel's gridlines to align with cells
  • Position buttons vertically for clarity

Pro tip: Make all related buttons the same width for a clean look!

Creating a Button Panel

Instead of random buttons scattered around, create an organized toolkit panel:

Visual Goal

┌──────────────────────────┐
│  Tensile Analysis        │
│      Toolkit             │
├──────────────────────────┤
│   1. Format Table        │
│   2. Calculate Stress    │
│   3. Create Chart        │
└──────────────────────────┘

Manual Method (Simple)

  1. Create 3 buttons using the steps above:
  2. Button 1 → FormatTensileTable → Text: "1. Format Table"
  3. Button 2 → CalculateStressStrain → Text: "2. Calculate Stress"

  4. Button 3 → CreateStressStrainChart → Text: "3. Create Chart"

  5. Align them vertically:

  6. Stack buttons on top of each other
  7. Make them the same width (~170 pixels)

  8. Leave small gaps between them

  9. Add a frame background:

  10. Insert → Shapes → Rounded Rectangle
  11. Draw behind your buttons
  12. Format: Light blue fill, dark blue border

  13. Right-click → Send to Back

  14. Add a title:

  15. Insert → Text Box
  16. Type: "Tensile Analysis Toolkit"
  17. Format: Bold, 12pt, centered

  18. Position above buttons

  19. Group everything:

  20. Select all elements (buttons, frame, title) - Hold Ctrl while clicking each
  21. Right-click → Group → Group
  22. Now it moves as one unit!

Automated Method (Advanced)

Want to create professional panels with one click?

Pre-Built Panel Creator

Download Create_Toolkit_Panel.bas and import it.

Then run: CreateTensileToolkitPanel

Result: Professional panel created instantly with:

  • Rounded frame with shadow
  • Styled title
  • 3 aligned buttons
  • Everything grouped together

Explore the code to see advanced VBA techniques!

Keyboard Shortcuts

Buttons are great, but shortcuts are faster when you're using the macro.

Setting Shortcuts

During Recording:

  • When you click "Record Macro", there's a "Shortcut key" field
  • Type a letter (e.g., F)
  • Excel adds Ctrl+Shift+ automatically
  • Result: Ctrl+Shift+F runs the macro

After Recording:

  1. Developer → Macros (or Alt+F8)
  2. Select the macro
  3. Click Options
  4. Set or change the shortcut key
  5. Click OK

Good Shortcut Practices

✅ Good Shortcuts:

  • Ctrl+Shift+F = Format
  • Ctrl+Shift+C = Chart
  • Ctrl+Shift+S = Stress calculation
  • Ctrl+Shift+B = Batch process

❌ Avoid:

  • Ctrl+C (already Copy)
  • Ctrl+V (already Paste)
  • Ctrl+S (already Save)
  • Ctrl+Z (already Undo)

Shortcut Conflicts

If you use a shortcut that Excel already uses, your macro takes priority! You'll lose the original function.

Always use Ctrl+Shift+Letter to avoid conflicts.

Quick Access Toolbar

For frequently used macros, add them to the Quick Access Toolbar (top-left corner):

  1. Click the dropdown arrow on Quick Access Toolbar
  2. Select More Commands
  3. In "Choose commands from", select Macros
  4. Find your macro (e.g., FormatTensileTable)
  5. Click Add >>
  6. Click OK

Result: One-click access from any Excel file!

Button Best Practices

Organization

For simple files:

  • 1-3 buttons near related data
  • Aligned vertically or horizontally
  • Clear, action-oriented text

For complex workbooks:

  • Create a dedicated "Controls" area
  • Group buttons by workflow
  • Use numbered steps (1, 2, 3)

Naming Buttons

❌ Vague:

  • "Macro1"
  • "Button"
  • "Run"

✅ Clear:

  • "Format Table"
  • "Calculate Stress & Strain"
  • "Export to PowerPoint"

Visual Hierarchy

Use formatting to show importance:

  • Primary action: Larger button, bold text
  • Secondary actions: Standard size
  • Dangerous actions: Red text (e.g., "Delete All Data")

Try It Yourself

Button Practice

Create a button panel for your Exercise 1 macro:

Steps:

  1. Create a button for FormatSpecimenData
  2. Position it in column G (right of data)
  3. Format: 150px wide, 30px tall, "Segoe UI" font
  4. Add a shape behind it (rounded rectangle)
  5. Add a title text box: "Formatting Tools"
  6. Group all elements together

Challenge: Can you create 2-3 buttons for future macros you'll make?

What You've Learned

✓ How to create clickable buttons linked to macros
✓ How to format and customize button appearance
✓ How to organize buttons into professional panels
✓ How to set keyboard shortcuts (during and after recording)
✓ How to add macros to Quick Access Toolbar
✓ Best practices for button naming and organization

Next: Real-World Examples

Now that you can record, edit, and create interfaces, let's see powerful real-world macros for material science workflows!

Continue to Real-World Examples →

← Back to Exercise 1 | Home