Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Here's the scenario: I want to send different pages of my pivot table report to different email recipients. I can do this by hand but it takes forever to change the filter, copy/paste, or export the report and then create and send the email, even just for a few people. And when I do this …
One thing I often found frustrating with VBA is the lack of any easy way to see the values in a range object. I often use Debug.Print when debugging my VBA and I'd love to just be able to Debug.Print a range like you can with other variables. But because a range is an object, …
Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a value after your code has finished. Let's say you want to keep track of the number of times something happens, like how many times someone clicks on this button they are not supposed to.
You might want to save your charts as images that can then be used in other applications, like email or displayed on a website. Selecting a single image then copying/pasting or saving it, isn't too much bother. But if you want to do that for several images it quickly becomes tiresome. I've written some VBA, …
Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality. What this code does is to clear any cells dependent on the value of another data validation list. I'll call these downstream cells. So if I change the Country, I'm going to clear the values …
In my previous blog post I created a progress bar using a userform. We can use this progress bar to communicate to our Excel user that our VBA code is still running. In this post I’m going to look at using the Status Bar to do the same thing, and create something like this. Status …
If your code runs for any noticeable amount of time, you might find that using some sort of visual feedback is useful for your users. Letting them know just how long something will take to complete, and just as importantly, letting them know that Excel hasn't hung, reassures them that the code is working and …
If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message like this: But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I’m going to use …
In a previous post I looked at using the FileSystemObject to search through folders on your computer's disk and create a hyperlinked list of files. Clicking on the hyperlink would open the file. FileSystemObject (FSO) can also be used to retrieve meta data from a file, like the creation date, last modified date or file …
If you are using data validation (and you should) to help you with your data entry, then you might find that the list you are using doesn't have all the values you want. For example, if you were using a list to assign a priority to some work, your list may consist of 'Not Started', …
