I had an interesting question from a reader recently. He wanted to save a PDF for each item in a data validation drop-down list. In the past, when I have tackled similar tasks, I have always used a named range with all the possible values, then looped through the cells in the named range. My Read More
Category: VBA
VBA code for charts and graphs in Excel
Charts and graphs in Excel have hundreds of different options. This is great for creating precisely the visualization we want but can be time-consuming to apply. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking. The short code snippets below will help Read More
Resize a UserForm with VBA or Windows API
When creating a VBA UserForm, we generally set it to a specific size. Most other forms and windows within the Excel and Windows environments do not have a fixed sized; they can be resized by the user. With a bit of coding magic, we can achieve a similar resizing effect for our VBA UserForms. This Read More
![Hide or disable a VBA UserForm [X] close button](https://exceloffthegrid.com/wp-content/uploads/2019/04/Hide-or-Disable-UserForm-Close-Button.png)
Hide or disable a VBA UserForm [X] close button
While working on a project recently, I thought about hiding the [X] close button which is at the top of the VBA UserForm. I managed to identify three options for achieving this. If you are thinking about the same thing, then this post will give you the answers you’re looking for. To hide, or not Read More
Closing VBA UserForms: Hide vs Unload
VBA UserForms are a key tool for managing user interactions. When a UserForm is well designed, it guides the user through the options and settings without any help file or guidance. From my own UserForm development, I know one of the most overlooked aspects is the closure of the UserForm itself. This post will cover Read More
Difference between Sheets and Worksheets in VBA
I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do. Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets. Yet, Sheets and Worksheets from Read More
Convert Merged cells to Centre Across Selection with VBA
To merge or not to merge? That is the question. And it’s a questions which divides the Excel community. We find the Merge & Center button is on the Ribbon, so it appears to be the only option. But soon we are greeted by these types of annoying error messages: Excel has a secret alternative Read More
Private vs Public Subs, Variables & Functions in VBA
we When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. On social media you can to set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends Read More
How to select all unlocked cells in Excel
Spreadsheets are easy to break. All it takes is one press of a key and suddenly every formula is displaying in an error message. That’s not good! This is why we apply worksheet protection, to prevent user error. Some think that worksheet protection is secure, however password protected worksheets can easily be unlocked with a Read More
Name an Excel Table when created
I love the Tables feature of Excel, it just makes everything else in the spreadsheet easier to work with. However, one of the biggest frustrations is that there is no option to name the Table at the time of creation. By default, we are given a unique name like Table1 if it is the first Read More