Up next in 10
Excel Add-Ins for Beginners: How to Create, Customize, and Use
8K views
Jul 6, 2024
Next: 1. Create Distributable Excel Add-Ins using XML and VBA: https://www.youtube.com/watch?v=Ykh3LIPDyvA 2. How to Use Images as Icons In Excel Add-Ins: https://www.youtube.com/watch?v=EUBU6g5WBKs Dive into the world of Excel add-ins with this easy-to-follow tutorial for beginners. You'll learn how to create an add-in, customize the ribbon, and ensure your add-in is available across all your workbooks. #excel #vba #exceltutorial 00:00 Introduction 00:17 Enable Developer Tab 00:34 Record a Macro or Write VBA Code for the Add-In 01:10 Save the Workbook as Excel Add-in (*.XLAM file) 01:45 Activate the Add-In 02:01 Customize Ribbon: Add a Button on Home Tab to Control the Add-in 03:00 Customize Ribbon: Add a New Custom Tab on the Ribbon 04:15 Remove an Excel Add-In 04:52 Customize Ribbon: Remove Custom Button and Tab
View Video Transcript
0:00
In this video, I will guide you through the process of creating an Excel add-in named
0:04
Toggle Gridlines using VBA. This add-in will allow you to toggle the visibility of gridlines
0:09
in your current worksheet. Additionally, we'll design a custom group and a button on the Home tab
0:14
enabling you to control gridline visibility with a single click. First, ensure the Developer tab is visible on the Excel ribbon. If it is not, click the File tab
0:23
and select Options. In the Excel Options dialog box, click Customize Ribbon from the left pane
0:28
Check the Developer box on the right pane and click OK. The Developer tab will now be available
0:33
on the ribbon. Now, open a new workbook and press Alt plus F11 to open the VBA editor
0:40
Insert a new module and create a subroutine named Toggle Gridlines in the Module Code Editor
0:45
Inside the subroutine, write an if statement to check whether the gridlines are visible
0:49
If the gridlines are visible, set Active Window, Display Gridlines to False to hide them
0:54
Otherwise, set it to True to make them visible. Close the VBA editor and return to the Excel window
1:00
Press Alt F8 to open the Macro dialog box. Select Toggle Gridlines from the list and click Run
1:07
The gridlines should now be hidden. Run the macro again to show the gridlines
1:11
To make this macro available in every workbook, save the workbook as an XLM file
1:16
This file format is used to save a workbook as a macro-enabled Excel add-in. Click the File tab
1:22
and choose Save As. Click Browse and in the Save As dialog box, select Excel add-in from the Save
1:27
As type dropdown. Notice that when you choose XLM, Excel will automatically change the save
1:32
location to the Add-ins folder. Provide a descriptive name for the add-in and click Save
1:37
When you save a workbook as an Excel add-in, a new add-in file is created and the original file
1:42
remains open. You can utilize its functionality by enabling it as an add-in. Click the Developer tab
1:48
then click Excel Add-ins in the Add-ins group. In the Add-ins dialog box, if your add-in is not
1:53
listed, click Browse, navigate to the add-in file, and click Open. Ensure the add-in is checked in
1:59
the list and click OK. Now restart Excel. To make the add-in easily accessible in every workbook
2:05
let's add a button to the Home tab. Click the File tab and choose Options. In the Excel Options
2:10
dialog box, click Customize Ribbon. In the Choose Commands from dropdown, select Macros. On the right
2:16
side, select Home and click the New Group button to create a new group. Select the new group
2:22
click Rename, and set the display name to BrainBell. Select the toggle grid lines from the left box
2:27
and click Add to insert it into the BrainBell group. Again, select the toggle grid lines in
2:32
the BrainBell group, click Rename, choose a suitable icon, and set the display name to
2:37
Toggle Grid Lines. Click OK to close the dialog box. Go to the Home tab and you'll see the new
2:42
BrainBell group at the end of the ribbon. Click the Toggle Grid Lines button to hide the grid
2:47
lines on the current sheet. Click the button again to show the grid lines. The add-in is now available
2:52
for any workbook, whether new or existing. Test the add-in on another workbook and you will observe
2:57
that it functions seamlessly like a built-in Excel command. You can insert the Toggle Grid Lines
3:02
button on any available tab in Excel or create your own custom tab on the ribbon. To add a custom tab
3:07
with your command button, go to Excel Options and select Customize Ribbon from the left pane
3:12
Click the Choose Commands from drop-down and select Macros. Find and select your Toggle Grid Lines
3:18
macro. Click the New Tab button on the right pane. This will create a new tab and a new group within
3:23
it. Select the new tab and click Rename to give it a descriptive name. Do the same for the new
3:28
group if desired. With the new group selected, find your Toggle Grid Lines macro, select it
3:33
and click Add to move it to your new group. Optionally, select the new button in your custom
3:38
group and click Rename to give it a user-friendly name and icon. Click OK to apply the changes and
3:43
close the Excel Options dialog box. Your custom tab with the Toggle Grid Lines button will now
3:48
be available on the ribbon, providing easy access to the add-in in any workbook. You can also adjust
3:54
the position of your tab on the ribbon. In the Customize Ribbon section, select your new tab
3:59
Use the up and down arrow buttons on the right side to move your new tab to the desired position
4:04
I moved it to the end of the list, just before the Help tab. Click OK to apply the changes and
4:09
close the Excel Options dialog box. The new tab will now be positioned at the end, just before
4:14
the Help tab. If you want to temporarily disable your add-in, go to the Developer tab and click
4:20
the Excel Add-ins button. Uncheck your add-in and click OK. Your add-in will no longer work
4:25
Test it by going to the Home tab and clicking the Toggle Grid Lines button
4:29
Excel will display a security notice, informing you that the automatic update of links has been
4:33
disabled. If you want to permanently delete the add-in, go to the Developer tab and click the
4:38
Excel Add-ins button. In the Add-ins dialog box, click Browse, navigate to the add-in file
4:43
right-click on the file, and choose Delete. Click Cancel to return to the Add-ins dialog box
4:49
Excel will automatically remove the add-in entry from the dialog box. To remove the button and
4:54
custom tab, go to Excel Options, select Customize Ribbon, and choose the Home tab from the right
4:59
pane. Click Reset and select Reset Only Selected Ribbon tab from the menu. This will reset the Home
5:05
tab and remove your custom button. To remove your custom tab, right-click on it and select Remove
5:10
from the context menu. This approach is suitable for a single PC. If you intend to distribute or
5:16
use your add-in on multiple computers, more advanced techniques are required, which I will
5:20
discuss in the next video. Thanks for watching. Please share, subscribe, and like my video
#Business & Productivity Software
#Software