Daily Dose of Excel

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

I imagine this is the way it is for most people:

Vast number of ideas of what I’d like to do
    A few get implemented as add-ins, utilities, tutorials, what have you
        Even fewer find their way to the website
            And, it takes even longer to find a proper location for very new topics.

I spent the better part of today adding one new tutorial, one new template, and hooking up several old pages that were already part of the www.tushar-mehta.com website but were never part of the site navigation structure. In the process, I added a new high level category called “Templates.” The following introduces each item.

Enhanced Find: This is a source form utility that I wrote for my own use. I imagine it would have been much simpler if Microsoft had added the UI ‘Find All’ feature to the Excel object model. But, it didn’t, and, in any case, the utility does a lot more than just ‘find all.’ To those who will want to tell me I should have used the TreeView control in the userform used to display the result: I did. {grin} Then, I decided it would be prudent to forgo it since I don’t know how that control finds its way onto a particular machine.

Chart Image to Data: I wrote this utility to help out someone who wanted to convert an image of a graph into the associated data points. No, it doesn’t use artificial intelligence, or OCR, or some fancy heuristic to figure out the data. {grin} Instead, it relies on the user first calibrating the image and then clicking on various points on a series to convert the click location into a data value.

The templates hierarchy: Several templates that I created for various reasons have languished on the website with no good place to put them. So, I finally broke down and added a high level category, Templates. As usual, whenever a file moves from one directory to another, I leave — or at least try to leave — a stub in the old location pointing to the new one.

Solver template to find a subset of entries that add up to a given total: One would expect that when someone makes a payment against a list of different charges (invoices), they would list which charges invoices are being paid. Yet, every so often we find a request for help from someone who has a total and now wants to find a possible subset of pending payments that total the payment amount. This template contains the necessary Solver set up for the task. Obviously, this is not an easy task and the ability to find an acceptable solution is limited both by the requirements and limitations placed on Solver and of the algorithm used by Solver.

A calendar template: Even if I say so myself, I think this is worth checking out. It’s a template that lets one generate a calendar for any year from 1900 to 3000 with the starting-day-of-week set to any weekday. The result can be in one of 2 formats — one month-per-page or 12 months-per-page. The design is clean and simple. And, best of all, no programming!

Compare sizes of TVs, computer monitors, and broadcasts: Over the past month or so, I replaced my dying TV with a HD TV and added a widescreen computer monitor. In the process, I spent a fair amount of time understanding what’s what with widescreen devices and HD broadcasts. This template addresses one specific aspect of that research: comparing the sizes of various devices. One thing I realized very quickly in my research was that a widescreen device would have to be somewhat larger than its standard counterpart if it was to have at least the same height. I did most of my work with algebra. But, then decided I’d share some of it in the form of a template. While creating the template I also realized it could be used to explain the bands that appear when one views a broadcast in one format (say widescreen) on an incompatible TV (i.e., a standard size TV). I also realized that the work in the template would form a good tutorial for data validation.

Data Validation I: There’s a whole bunch of stuff I have on data validation that I’d like to share. Given the big weakness in Excel’s native data validation (copy+paste into a cell to wipe out the validation criterion in it!), I prefer alternatives. This tutorial documents some simple ways to validate data without Excel’s Data Validation or VBA. There’s a whole bunch more I will share in the weeks/months to come.

Userform coding interface: I started work on a VBA chapter — and it is far from complete — that addresses two favorite topics of mine: (1) The structure of the interface between the code that manages a userform and the code that does the actual task of a utility, and (2) The RefEdit control including a workaround that lets one simulate its functionality in a modeless userform, together with a class module that lets one “drop” the solution into any userform.

You can use Data Validation to force your users to enter text in upper case. Select the cell where you want to force upper case and select Validation from the Data menu. Assuming you selected cell A3, make your validation screens look like the pictures below. I don’t have any input message, but you certainly could. If you use a cell other than A3, be sure to change your cell references in the formula.

I've been rewriting some user spreadsheets.

One thing I noticed after a while was that there isn't an easy way to manage Conditional Formats.
As you may know, Conditional Formats use formulas too, so they can be very important to get right - especially if these cells are relied on to highlight errors.

Excel provides some limited ways to manage Conditional Formats.
There's the editor itself: from the Format menu, Conditional Formatting...
and then there's the Goto feature: from the Edit menu, Go To..., Click Special..., Select 'Conditional Formats' (you then have a choice between All or Same).

Those can be challenging to use if you're dealing with a sheet full of various formats.

I put together a userform to list all of the Cells which contain Conditional Formats.
If groups of Cells contain the same Conditional Format then they appear in the same list item.

Click a List Item to select the cells.
Doubleclick a List Item to edit the Conditional Format.

I've not provided an XLA add-in - I'll leave that to you. Here are instructions for building the userform.

Create a new Userform and drop a ListView control onto it. Rename the ListView control: lvwAddress
(Don't have a ListView control in your Toolbox? Right-click in the Controls area, select Additional Controls..., tick Microsoft ListView Control)
Open the code for the Userform and drop the following code in:

You would run the userform with this statement:

It's interesting to note that some simple tweaks to the above code would provide you the same management of Data Validations:
1. Change the value for the Const cCaption
2. Change xlCellTypeAllFormatConditions to xlCellTypeAllValidation
3. Change xlCellTypeSameFormatConditions to xlCellTypeSameValidation
4. Change xlDialogConditionalFormatting to xlDialogDataValidation

In Conditional Data Validation, I described how to set up conditional formatting to alert you when your list validation was no longer valid. I also noticed that this is covered in Professional Excel Development (and much better, I might add) so if you haven't ordered your copy yet, I suggest you do.

I recently had cause to use this technique, but my lists were all over the place. I couldn't set up the CF on one cell, then copy it to the others because the relationship between the cells wasn't the same as the relationship between the lists. I created this macro to loop through all the cells on the sheet and if there's list validation, it adds conditional formatting.

Data Validation is pretty useful for making sure that appropriate data is entered into a cell. If you've been reading this blog, you know I'm fond of DV despite its problems.

One of the things that I like best about DV is the In Cell Dropdown. That little dropdown arrow that goes away when you're not in the cell. Sometimes I use Data Validation to have a handy list in the cell, but I don't really want to validate the data. I want to be able to type in anything I want, but still have some commonly used items in a list.

For me, Data Validation is useful in that situation also. The key is to remove the error portion. Take this example where I have a list of three cities in the DV for cell A1. I use these three cities frequently, but I need to be able to type in less frequently used cities also.

On the Data Validation dialog, simply select the Error Alert tab and remove the checkbox so that no errors are generated.

While you're there, check out the other "error" options.