We can often find spreadsheets with cells containing codes that have been shortened. So, rather than Proj003412, the cell value has been reduced to just 3412. It’s not a big problem to extend the code back to the full version; we could achieve that with a simple formula, then copy and paste special values. If Read More
Category: VBA
Convert color codes (RGB, HSL, Hex, Long, CMYK)
I often find myself converting between different formats of color codes; especially from hex to RGB and back again. Rather than firing up Google and searching for color conversion tools online, I decided to write my own User Defined Functions in Excel, now I can calculate the result within a worksheet instead. The functions in Read More
Apportion a value across cells with VBA
Today, I want to share a really useful time-saving macro with you. It’s a macro to apportion a value across cells. Now that I’ve written it, I’m starting to gain the benefits, and hopefully, you can too. The filename for this post is 0005 Apportion value across cells.zip. When to use the macro? Let’s say you’ve Read More
3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a Read More
Remove blank rows in Excel
Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this Read More
Application.CutCopyMode = False
If you use copy and paste while recording a Macro, you will see the code which refers to Application.CutCopyMode = False. You may wonder why it’s there? The recorded code looks a bit like this: Range(“A1:A10”).Select Selection.Copy Range(“D1”).Select ActiveSheet.Paste Application.CutCopyMode = False If you use VBA to copy and paste without that line of code, Read More
Loop through each item in a Data Validation list with VBA
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
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
