Prevent Invalid Entries in Excel UserForm

In an Excel UserForm, you can use combo boxes, to show users a drop down list of items. If all goes well, the users will select an item from the list, and life will be good.

However, the occasional user likes to get creative, and types an entry that isn’t in the drop down list. And that can cause problems!

To restrict those creative users, you can set the MatchRequired property for the combo box to True.

userformcombomatch02

Add a Select Item

Changing that MatchRequired property can cause problems though. In this example, the Excel VBA code also tries to clear out the combo boxes, after adding an order to the parts database sheet.

Because an empty string isn’t a valid entry, an error message appears — “Invalid Property Value”.

userformcombomatch03

To prevent that error from appearing, you can add “Select” as the first item in the drop down list, and reset the combo boxes to that item, instead of clearing them out.

userformcombomatch01

Download the Sample Combo Box Workbook

To download the sample file, go to the Excel Templates page on my Contextures site. In the UserForms section, look for UF0026 – Prevent Invalid Entries in UserForm.

The file is in Excel 2003 format, and zipped. Enable macros after opening the workbook.

Watch the Excel UserForm Combo Box Match Video

To see the steps for changing the MatchRequired setting, and editing the code, you can watch this short Excel video tutorial.

_________________

Delete Excel Drilldown Sheets Automatically

With the pivot table Show Details feature in Excel, a new sheet is inserted when you double-click on the value cell in a pivot table.

It’s a great feature for drilling into the details, but you can end up with lots of extra sheets in your workbook.

showdetails03

Usually, you don’t want to save the sheets, so you manually delete them before you close the file.

Automatically Name the Sheets

With event code on the pivot table’s worksheet, and in the workbook module, you can add a prefix – XShow_ – when these detail sheets are created.

showdetails06

That prefix should make the sheets easier to find and delete.

Automatically Delete the Sheets

To make the cleanup task even easier, you can use event code to prompt you to delete those sheet when you’re closing the workbook.

showdetails04

If you click Yes, all the sheets with the XShow_ prefix are deleted. Then, click Save, to save the tidied up version of the workbook.

showdetails05

See the Drilldown Sheet Code

For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.

Download the Sample Drilldown File

To see how the event code names the sheets, and deletes them when closing, you can download the Pivot Table Drilldown sample file.
________________

Would You Approve This Excel Book Cover?

There is a list of Excel books on my Contextures website, and it hasn’t been updated recently. Today, I checked Amazon, to see what new and exciting Excel books had been released, so I could start adding them to the list.

New Excel Book

There’s a book – Excel 2010 Made Simple, by Abbott Katz — with a release date of May 31, 2011, from Apress, the publisher of my pivot table books.

Amazon lists the book as “not yet released”, but they show the cover, which you can see below.

Excel Book Cover wtih 3-D chart
Excel Book Cover wtih 3-D chart

Excel Charts Chapter

One of the chapters is on Excel charts, and the book blurb promises that you’ll learn “How to create colorful, meaningful charts”.

I hope the cover chart was selected by someone in the Apress marketing department, and not the author!

Missing Books

I’ll be updating the list of Excel books over the next few weeks, so if you know of any recent books that are missing from the list, please let me know, so I can include them. Thanks!

Make a Simple Pie Chart

And if you do need to make a pie chart in Excel, for a business report, or for the cover of your next book, keep it simple!

This video shows how to make a basic pie chart in Excel, then add formatting, labels, and other features. Use your Excel charting powers for good, not evil!

Excel Chart Links

If you want to learn more about Excel chart, but not the 3-D rainbow-coloured kind, check out the tutorials at the following links:

Box Plot Chart (Box and Whisker)

Line-Column Chart 2 Axes

Cluster Stack Chart

Pie Charts

Waterfall Chart

In-Cell Charts

____________

Excel Weight Tracker Metric

Last year, you might have seen the Excel Weight Loss Tracker workbook that I posted, which used inches and pounds as its measurements.

Even though we use the metric system in Canada, we still track our weight in the Imperial system. (I’m not sure why.)

Stone/Pound Version

In May 2010, I posted the weight loss tracker in a Stone/Pound version, for the people who use that system.

Note: Please consult your doctor for recommended target weight and weekly weight loss goals. This workbook is designed as a recording tool only, based on the goals that you set with your doctor’s advice.

[Update] The latest version of this workbook lets you select pounds or kilograms as the measurement. To download, see the link at the end of this article.

Metric Weight Tracker

If you’d prefer to track your weight using the metric system, you can download the latest version, which uses kilos. In this version, you can enter your height in centimetres, and your weight in kilograms.

The BMI and ideal weight range are calculated based on those measurements.

The good news is that your weight is a lower number in kilograms, compared to pounds. The bad news is that your pants don’t fit any better!

Metric Excel Weight Tracker
Metric Excel Weight Tracker

Download the Excel Weight Tracker Metric

You can download a zipped copy of the Excel weight loss tracker, and try it for yourself.

The sheets are protected, with the green cells unlocked, and there’s no password on the worksheets.

There are versions for Excel 2007/Excel 2010, and Excel 2003 on the Contextures website: Excel Weight Loss Tracker

And remember, I’m not a trained medical professional, so check with your doctor for personalized weight management advice.
_____________

Too Few Rows in New Excel Workbook

In Excel 2007 and Excel 2010, when you create a new workbook, there should be 1,048,576 rows on the worksheet.

Excel2007NewSheet01

Not Enough Rows

However, one of my clients was creating new files in Excel 2007, and the sheets only had 65,536 rows, just as they did in older versions.

Excel2007NewSheet03

Perhaps you don’t need more rows than that, but if you’ve paid for a shiny new version, you’d like access to all of its features!

Solve the Too Few Rows Problem

At first, we thought the problem might be an old Excel 2003 template, that was starting automatically, and being used for the new workbooks.

A search of all the Templates folders didn’t turn up any suspects, so that theory was wrong.

Default Save Format

Finally, we discovered that the default format for saving files was set to Excel 97-2003 Workbook (*.xls).

Excel2007NewSheet02

Change the File Format Setting

To get the full-sized Excel 2007 worksheets, follow the steps below:

  • First, go into the Excel Options.
  • Then, at the left, click on the Save category
  • Next, at the right, in the Save Workbooks section, select one of the newer formats as the default for saving files.
  • Finally, click OK, to close the Options window

Excel2007NewSheet04

All the Rows!

After you change that setting, the problem should disappear.

Now, when you create a new workbook, its sheets will have 1,048,576 rows.
___________