Learn how to create Excel dashboards.

Categories

30 Excel Functions in 30 Days

 

Archives

Counting Query Tables in Excel

A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site.

This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.

Testing in Excel 2010

Several people have asked about an update, so I installed the PivotPlay PLUS add-in in Excel 2010, to test it. Instead of showing up on the menu bar, the start button appears on the Excel Ribbon’s Add-Ins tab.

If the active worksheet has a pivot

Continue reading Counting Query Tables in Excel

Click to Move Excel List Items Up or Down

Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.

Here’s how the tool works:

Click a button to select a group of cells that you want to work with, like this list of months. Then, click the up and down arrows, to scroll the list through the selected cells. When you’re done, click the Exit button, to “release” the scroll area.

In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February

Continue reading Click to Move Excel List Items Up or Down

Excel UserForm with Dependent Combo Boxes

In an Excel UserForm, you can add combo boxes to make data entry easier. The UserForm in this example is used to enter parts information onto a hidden inventory sheet.

Before selecting a Part ID, you have to select a Part Type. When the Part Type combo box is updated, code runs, and creates a list of parts, for the selected part type.

How It Works

In the background, an Advanced Filter runs, to create a worksheet list of parts for the selected part type.

That list is set up as a named range, and the range name

Continue reading Excel UserForm with Dependent Combo Boxes

Show Data Validation Message in Text Box

When you’re setting up data validation on a worksheet, you can include an Input Message, to help anyone who’s using the workbook.

You’ll have to get to the point quickly though – the message is limited to 255 characters.

There are other limitations too – you can’t control the size of the text box, and you can’t change its font size or fill colour, unless you change your Windows settings.

Show Input Message in a Text Box

As an alternative to the Input Message popup, you can show a message in a text box, at the top of the

Continue reading Show Data Validation Message in Text Box

Print Invoices With Excel Macro

There is a sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.

I’ve updated the file, and you can now download the xlsm version, if you’re using Excel 2007 or a later version.

Store Invoice Details in Table

In the new version, the invoice data is stored in a named Excel table, and a named range – Database – is based on that table. The range is dynamic, because it is based on the named table, so it will include new rows as they are

Continue reading Print Invoices With Excel Macro

Excel Date Picker Tool

If you’d like a quick and easy way to add dates in a worksheet, you can use this handy date picker tool, from Jim Cone. It opens to the current date, and you can scroll through months and years, by using the scrollbars at the top of the date picker form.

Just select a cell, and click the insert button, to add the date. If you hold the Shift key, and click the Insert button, it will append the date to the cell’s contents.

Create Calendars on a Worksheet

In addition to inserting the date, Jim’s date picker

Continue reading Excel Date Picker Tool

Problems With SendKeys in Excel

Yes, I know that it’s a bad idea to use the SendKeys method in Excel, because strange things can happen.

However, it’s handy in a few situations, and I use SendKeys in a few of my Comments macros.

For example, in this macro to insert a blank comment, without a user name, the comment opens for editing, at the end of the macro.

Sub CommentAddOrEdit() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment text:="" End If SendKeys "+{F2}" End Sub Send Keyboard Shortcuts

In that example, the SendKeys line simulates using the keyboard shortcut

Continue reading Problems With SendKeys in Excel

Add Custom Ribbon Tab For Workbook

Last week, you saw how to open and edit the Ribbon code in an Excel file that has a custom tab. This week, you can see how to create a custom tab in an Excel workbook, and add buttons to run your macros.

This example is based on an Order Form workbook, and the buttons run macros to clear the data entry cells, and to view the file before printing.

You’ll see how to set the custom tab labels and icons, and change your macros so they run from a click on the Ribbon. Exciting, right?

Watch the

Continue reading Add Custom Ribbon Tab For Workbook

Custom Ribbon Tab for Excel File

Have you created any custom tabs for your Excel files? I avoided that, as long as possible, but have finally started dabbling with the Ribbon code. And, the good news is that it’s not as dark and scary as I imagined.

Here is a screen shot of a sample file that I created, as an update to my Parts Database which has a UserForm for data entry.

When the file opens, it adds a custom tab to the Ribbon, with buttons to run the workbook’s macros.

You can download the sample file, and see the written instructions here: Getting

Continue reading Custom Ribbon Tab for Excel File

Excel 2010 Runs Macros Without Enabling

In the old days, if you opened a file that contained macros, a warning message popped up on the screen. Here is the message from Excel 2003, giving me the option to Enable Macros or Disable Macros.

Of course, you only saw that warning if you had your security level set to Medium, which allowed you to choose whether or not to run macros.

Security Bar in Excel 2007

Things changed in Excel 2007, and there was a security bar, instead of a popup warning. This caused problems, because many people didn’t notice the bar, and complained that

Continue reading Excel 2010 Runs Macros Without Enabling

Related Posts Plugin for WordPress, Blogger...