All articles in 'VBA Macros' Category

CP005: Introduction to Form Controls – an interview with Debra Dalgleish

Published on Apr 17, 2014 in Chandoo.org Podcast Sessions, VBA Macros
CP005: Introduction to Form Controls – an interview with Debra Dalgleish

In the 5th session of Chandoo.org podcast, we are going to demystify form controls.

I am very happy and excited to interview my good friend, fellow Excel MVP, author, blogger and virtual mentor – Debra Dalgleish about this topic.

In this podcast, you will learn,

  • What are form controls
  • When you would use them?
  • Example form control – Combo box
  • How form controls differ from active-x controls
  • How to enable form controls in your Excel?
  • Various important form controls
  • Special bonus & how to obtain it
Continue »

Hui’s Calendar Tool (as Borrowed from Chandoo)

Published on Apr 7, 2014 in Automation, Huis, Posts by Hui, VBA Macros
Hui’s Calendar Tool (as Borrowed from Chandoo)

Today we take a look at an exercise in how to develop a simple Calendar selection tool for your worksheets without using complex VBA Calendar Controls.

Continue »

VBA Serenity

Published on Mar 31, 2014 in Posts by Jeff, Random, VBA Macros

Greetings and salutations, my fellow VBA-fearin’ congregation. Evangelizin’ Jeff here, spreading the good word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a high-yah pow-wah. Our […]

Continue »

Tables, PivotTables, and Macros: music to your ears

Published on Mar 28, 2014 in Posts by Jeff, VBA Macros

Howdy folks. Jeff Weir here again. You may remember me from posts such as What would James Bond have in his Personal Macro Workbook and my now infamous music review. Today – and this truly will be music to some ears – we’re going to concentrate more on the former and less on the latter. […]

Continue »

Dynamic (Cascading) Dropdowns that reset on change

Published on Feb 13, 2014 in Excel Howtos, Posts by Jeff, VBA Macros

Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall […]

Continue »

Making a slick on/off switch using Excel & little bit of VBA [case study]

Published on Dec 5, 2013 in VBA Macros
Making a slick on/off switch using Excel & little bit of VBA [case study]

I have a confession to make.

I am not sure how to describe this new thing I made in Excel / VBA. So first take a look at it.

Read on to learn how you can create something like this using Excel & a little bit of VBA.

Continue »

Secret Agent KV’s Chops…what’s in HIS Personal Macro Workbook?

Published on Nov 19, 2013 in Posts by KV, VBA Macros

Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that […]

Continue »

What would James Bond have in his Personal Macro Workbook?

Published on Nov 18, 2013 in Posts by Jeff, VBA Macros
What would James Bond have in his Personal Macro Workbook?

You know, I‘m constantly amazed at the depth of knowledge and wisdom that comes up in the comments of this site. For instance, the other day, undercover Excel secret agent KV came up a great list of interview questions in response to Chandoo’s great article What are best Excel interview questions?

I particularly liked this one:
Do you have a Personal Macro Workbook setup ? If yes, what are the most common macros you use in your daily work?

Secret Agent KV obviously has some purpose-built gadgets that he employs to kill inefficiencies. And he obviously also knows the two top-secret reasons to keep code in a Personal Macro Workbook:

  1. To keep it safe from international spy rings.
  2. So you can whip it out whenever you are cornered by any evil-looking spreadsheet.

While that first point is important, that second is the clincher: code stored in a personal workbook can be unleashed with a simple judo-chop to the throat.

Today, lets understand how to setup your personal macro workbook and open a secret passage-way to Excel awesomeness.

Continue »

Show pop-up calendar upon right click [Excel VBA]

Published on Nov 13, 2013 in VBA Macros

There are times when we are entering dates into several columns and would like to select a date from a popup calendar instead of manually typing.

Today, lets understand how we can set up a pop-up calendar in Excel so that your users can easily input dates by right clicking on a cell and inserting a date.

pop-up calendar vba excel

Continue »

Using Arrays To Update Table Columns

Published on Sep 16, 2013 in Automation, Excel Howtos, VBA Macros

Using Arrays To Update Table Columns We are creating a lot of reports everyday and these reports contain a lot of data which is presented in various styles as per the requirements. The data that allows us to create the reports is usually referred as raw data and in most of the cases is stored […]

Continue »

Excel Risk Map

Published on May 28, 2013 in Automation, Project Management, VBA Macros
Excel Risk Map

We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.

Continue »

Learn Top 10 Excel Features

Published on Apr 16, 2013 in Charts and Graphs, Learn Excel, Power Pivot, VBA Macros
Learn Top 10 Excel Features

Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,

I analyzed the data and here are the top 10 features in Excel according to you.

Read on to learn more.

Continue »

How to create interactive calendar to highlight events & appointments [Tutorial]

Published on Apr 9, 2013 in Charts and Graphs, Learn Excel, VBA Macros
How to create interactive calendar to highlight events & appointments [Tutorial]

One of the popular uses of Excel is to maintain a list of events, appointments or other calendar related stuff. While Excel shines easily when you want to log this data, it has no quick way to visualize this information. But we can use little creativity, conditional formatting, few formulas & 3 lines of VBA code to create a slick, interactive calendar in Excel. Today, lets understand how to do this.

Continue »

Show hide list boxes using VBA

Published on Dec 18, 2012 in VBA Macros
Show hide list boxes using VBA

Rama, one of our readers emailed this:
I Have Many List boxes In That I need to Hide Few Of them Using Check box

Show Hide list boxes by using a check box

We can use check box and a bit of VBA to do this easily. Something like above demo.

Read on to learn how to do this using Excel & VBA.

Continue »

Macros for Automatically Implementing Modeling Best Practices

Published on Nov 29, 2012 in Financial Modeling, VBA Macros
Macros for Automatically Implementing Modeling Best Practices

In the first part on our Modeling Best Practices series, we learned 5 best practices to follow. This article shows how to automatically implement the best practices using macros.

Continue »