AutoFilters are a great feature in Excel. Often they are a quicker way of sorting and filtering data than looping through each cell in a range. This post provides the main lines of code to apply and control the AutoFilter settings with VBA. Adapting the code to your needs Every code snippet below is Read More
In VBA, there are several ways to run a macro within a macro, and even run a macro from another workbook. On forums these methods are used interchangeably, which can lead to confusion for others, as depending on the method for running/calling the macro, the syntax is slightly different. This post shows these common methods Read More
Excel has zoom settings for worksheets, UserForms and print settings. Each of these serves different purposes and used at different points, but they can all be controlled by VBA. Worksheet zoom settings The worksheet zoom setting in Excel will always be an integer. If the value set by VBA contains decimal places it will Read More
Often we want to determine what type of object has been selected in Excel. It helps with error checking prior to running a piece of code. Basic code This action is just a just a single line of code: Sub testSelection() MsgBox TypeName(Selection) End Sub Incorporate the check into a Select statement If Read More
When recording a macro, Excel includes all the scroll bar movement in the code. Normally, we delete this code, as we just don’t need it. But occasionally, being able to control the scroll bars and their display properties is useful. Display or Hide Scroll Bars Scroll bars can be displayed or hidden. It is Read More
It is frustrating that Excel, PowerPoint and Word work natively using a measurement known as points. However, measurements within the standard application menus are set in centimeters or inches. This can be seen when setting the position and size of a shape in PowerPoint through the standard menus. Yet, when using a macro to retrieve Read More
Since Excel 2010 it has been possible to save Excel files as PDF documents. The code below provides the VBA options to do achieve this. These VBA Code Snippets can be used in much larger processes to automate the distribution of hundreds of document in just a few seconds. Saving Excel workbooks, sheets, charts Read More
Password protecting an Excel file achieves two main objectives (1) prevents unauthorized access to the Excel file (2) prevents unauthorized modification of the Excel file. File-level protection is not the same as worksheet protection or workbook protection. Worksheet protection controls what a user can or cannot do on a worksheet, such as inserting rows or Read More
In a previous VBA Code Snippet, we covered protecting and unprotecting worksheets. In this post, we will consider protecting and unprotecting workbooks. The two topics are closely related, so if this post does not provide the information you need, then read that post too. Before getting started, there is an important change in Excel 2013 Read More
Protecting and unprotecting worksheets is a common action. There is nothing worse than somebody who doesn’t know what they’re doing over typing important formulas and cell values. Many advancing VBA programmers will start to consider how to apply worksheet protection automatically with Macros. The VBA Code Snippets below show how to do most activities related Read More