There is a long list of built-in dialog boxes available in Excel. We can access and open those dialogs, and Windows dialogs too, with VBA code. In this post, we see how to launch and use file dialogs in Excel using VBA macros. We will see how to select a folder or file, and open or save a file (usually an Excel workbook). We also compare and discuss advantages and disadvantages of the different methods.
There are many tools to create mind maps available online. But guess what, we can also create mind maps in Excel using this simple macro. The macro adds a shape of the size of the selected range whenever some text is added to a cell/range. The following shapes are linked to the root shape, which is either the first one added or any other shape that has been set as root.
We can use Microsoft Excel to send and extract/read emails via Outlook or even via some other email service such as Google mail. In this post we see how to do that. But wait, why would we want to do that in the first place? We can use the data in Excel to address the email to a particular group, and with specific information stored in Excel, or send multiple emails at once, schedule emails, track email status, etc.
CompareXLS is an Excel Add-in that allows comparing workbook properties, worksheet properties and content, and datasets (columns, groups of cells, etc) in Excel. This Excel Add-in prompts three dialogs or user forms to choose two workbooks, two worksheets, or two datasets, respectively. The two worksheets or datasets can both belong to the same workbook or separate workbooks. Other options allow showing properties of the current workbook, worksheet, or selected range, as well as listing all worksheets in the workbook, sorting sheets alphabetically, or un-hiding all worksheets.
In a previous post we have seen how to create a simple registration system in Excel using ActiveX controls and VBA macros. In this post we see how to input the data through a Userform. Excel Userforms are very powerful and allow adding more control to the data entry process and the interaction with users. In this example we create a Userform to register employees into the system. The records are saved to the active worksheet, but could also be saved to a different file, or a DB. When using the same workbook, we may want to protect the workbook structure and/or worksheet altogether – see more about workbook protection here: Protect Selected Workbooks Excel VBA Macro
In this post we see how to create a flowchart in Excel with data from a selected range. We can do that with just one click using VBA macros. It is a simple example to add and manipulate shapes in Excel programmatically. The macro loops through the cells in the selected range, and adds a shape for each cell and connectors between the shapes to show the process flow. This flowchart creator in Excel can easily be leveraged to change the orientation of the process flow or add other flows to the chart. An example of such is the Excel Org-Chart Creator app published earlier in this blog, which creates a chart from a range of data organized by tier and reporting levels. It is ideal to display the team or the organization structure.
In a previous post we have seen how to create a simple registration system in Excel using basic VBA macros. Such system consists of a registration form where users can enter the data (an onboarding form with the onboarding date, employee name, role, etc., in this example), and a registry that stores all the entries. In this post we see how to use form controls to create the registration form instead of using a range of cells. We will specifically be using ActiveX controls in Excel, which are programmable controls that allow to add functionality to the form.
Excel Date Picker is a simple Excel add-in that allows to quickly insert a date in a cell from a calendar pop up where you can easily navigate to pick the date. Excel Date Picker adds an item to the cell drop-down menu that appears when right-clicking the cell, which takes you to the calendar date picker. As simple and fast as that!
In this post we learn to create a foreign exchange currency converter in Excel. We do not necessarily have to use macros to do so, but may need to code macros in some scenarios. First we need to get the current exchange rates from the internet. We can get that from a website through a web query, or scraping the data, or from an API. In this article we will see how to do it with a web query by either manually importing web data into a query table or using a macro to add the web query to a worksheet (which was already covered in this other post: Add Web Query To Worksheet).
This post showcases a simple but very useful registration system in Excel. It is a classic when learning Excel VBA, and consists of a registration form where users can enter some data, and a registry list that stores all the entries. As an example, we have an on-boarding registration system, but this could apply to any other registration process such as purchase order submission registration, invoicing, incidents, etc. In this example, the registration form is in the first worksheet and the registry in another worksheet, but it could all be in the same worksheet too.
Excel Forex Tester simulates trading in the Forex market using historical data. It allows to easily place market or pending orders, setup targets, and calculate profits. Additionally, this Forex Tester in Excel comes with a number of technical indicators, and can potentially be leveraged to plot customized indicators.
Excel File Explorer gives the opportunity to dig into a given directory and extract the whole hierarchy of folders and files into a worksheet. The result shows indented folder names, and the name and size of the files they contain.
Excel Service-Desk Manager is a simple but powerful service incident management tool that can be customized and potentially leveraged for the overall service management cycle. Excel Service-Desk Manager allows to create, update and resolve incidents, setting the status, priority, assignment team, and fully tracking all changes. It comes with a dashboard and simple reporting table.
