Daily Dose of Excel

I have a cell with data validation. It's set as an in-cell dropdown list and contains two items: Yes and No. I want to make it so that I can double click on that cell to toggle between yes and no. But wait, that's not good enough. What about longer lists? Yes, I want something that will iterate through all the items on a data validation list by double clicking. Dare I dream.

Here's my first go at it. I figure it's going to need some work, like what happens when the user double clicks on something that's not a range, but it's a start. I've basically handled two types of lists: the kind where you hard code values separated by commas (international issue here?), and the the range reference. Oh, and I need to test named ranges, but I think they'll work.

This code is in the sheet's class module (Sheet1 in my case). Test it out if you like. Let me know if you see any errors or better ways.

P.S. Why is Target.Validation always something (that is, Not Nothing) even if the cell doesn't have validation?

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 have a table of numbers and formulas for each row, column, and for the table as a whole. The table is part of a report - the output of the application. The user wants to exclude certain numbers from the calculations after reviewing them. These numbers would be outliers and would skew the results. The calculations are AVERAGE and STDEV functions. If a number is excluded, it needs to still be shown on the reports, but with a strikethrough format.

The obvious course is to modify the formula when the user has identified a cell to exclude. With formulas for every row, column, and for the whole table, that's a pretty big job. An easier way is to change the numbers to text. Both AVERAGE and STDEV ignore text, so this would have the effect of excluding the numbers from the formulas without having to change the formulas. I started with something like this:

This is in the worksheet's BeforeDoubleClick event. I use the strikethrough property to determine if the number has already been excluded. The user can double click the number to toggle between inclusion and exclusion. Excluded numbers have an apostrophe put in front of them (making them text) and the font is changed to strikethrough. Included numbers are changed back to a Double (using CDbl) and the strikethrough is removed.

Incidentally, not every number can be excluded. I've applied a particular style to those numbers that can be excluded and I limit the event like this:

A new wrinkle appeared. Now some of the numbers are actually formulas. That complicates the above code snippet a little.

Instead of putting an apostrophe in front of the value to make it text, I surround it with the TEXT function. This has the added benefit of keeping the same number format applied to the text as was applied to the number. When a number is excluded (the Else part), I start with "=TEXT(". Then I repeat the existing formula, removing the equal sign if there was one (Mid(.Formula, lStart, Len(.Formula))). The suffix to this string manipulation is the existing NumberFormat surrounded by double quotes.

When a number is included, the TEXT portion of the formula is removed. The Mid function starts at Len("=TEXT()"), which is a verbose way of saying 7. The length of Mid is the length of the formula, minus the length the text function (including parentheses, the comma that separates the number format argument, and the quotes that surround the number format), minus the length of the numberformat.

This has the strange side effect of converting a number like 3 into a formula like =3 when it's toggled. I can't think of any ill effects of that, but there may be.

I have an application that displays a list of "documents" for a particular product. Most of the documents are pdfs, but some are iso images for burning a CD. The display sheet shows each CD associated with the product, and for each CD the iso, the label that's stuck to the CD, and the pdfs that should accompany it. The user selects the product, operating system, and version from comboboxes to display the appropriate documents.

Normally, the code for a combobox lives in the containing sheet's class module (like Sheet1). However, good coding practice dictates that all of the VBA be in the add-in and not in the template. To accomplish this, I made a custom class module to house the three comboboxes and respond to their events. The class is named CSheetEvents and contains these variable declarations:

The variables are private to the class module and I use Property Get and Property Set procedures to assign them. An example is:

When the user selects the appropriate menu item, the code opens the template and assigns the three comboboxes. The relevant portion of that code is:

The variable gclsSheet is a global variable that holds an instance of the CSheetEvents so that it doesn't go out of scope. With the comboboxes properly assigned inside the class, they will now respond to events. The change event of cbxProduct fills cbxSystem. The change event of cbxSystem fills cbxVersion. And the change event of cbxVersion lists the CDs and hyperlinks to all of the documents.

When the user has printed the documents, he closes the sheet. Since I've used a template, there is an unsaved document named AllCDConfigurations1 - a workbook created from the template AllCDConfiguration.xlt. I don't want the user to be faced with a message to save this document, so I also included a workbook object in my class module. By defining a workbook variable with the WithEvents keyword, I can capture the Before_Close event and eliminate that save message.

The Cancel = True part stops the normal close operation and the mobjWb.Close False part closes the workbook without saving changes.

Excel doesn't provide events for the deletion of rows and columns. There are two methods that you can use to determine if a user deletes a row, and they are described here. If you want to detect column deletion, the process is the same, but some of the details change.

Monitored Public Variable

The first method counts the number of rows used in the spreadsheet, then recounts whenever something is changed. It uses the Worksheet_Change event which fires whenever the user changes any cell in the worksheet including row deletion. Start by creating a public variable in a standard module:

This will hold the number of rows used in the spreadsheet before the change. We'll compare it to the number of rows after the change to determine if one has been deleted. In the worksheet's class module, set up an activate event and a change event.

Use the dropdown boxes at the top of the code window to insert the Sub and End Sub statements. The activate event sets the initial value of the public variable whenever the sheet is activated. In the change event, the current number of rows are counted and compared to the public variable to determine if there are fewer.

One downside to this method is that you can't prevent the deletion, only know that it happened. There's also a lot of overhead associated with this method. The change event is fired every time something on the sheet is changed. You could work for days or years on this worksheet changing cells without deleting a row. All those precious computer cycles wasted.

Custom Class Module

The second method attempts to capture all the ways that a user can delete a row. The danger of this method is that you may miss a way. I can only see two ways to delete a row: the Delete control on the Row right click menu and the Delete control under the Edit menu. I don't know of any keyboard shortcuts to delete an entire row.

To use this method, we'll create a custom class module with two variable; one for each of the controls. We will then monitor the click events of those commandbar controls to determine if the user clicked them. Start by creating a custom class module (Insert > Class Module) . Rename it to CCbarEvents in the Properties Window (F4).

Next create a global variable to hold your custom class module - create this in a standard module:

and use the workbook's open event to initialize the class module whenever the workbook is opened:

Now the class module will be "live" as long as the public variable is in scope, i.e. for as long as the workbook is open. Next we have to put some stuff in the class module that does some work.

I declare two variables using the WithEvents keyword to expose the events to our class module. The variables are assigned to the appropriate commandbar controls in the class' initialize event. I found the IDs of the commandbar controls by going to the immediate window and typing:

Note that the caption for Edit > Delete changes if a row or column is selected instead of a cell. The class' initialize event is fired when I establish the class in the Workbook_Open event. Next I create the click events for the CommandBarButton variables. For mCellDelButton, I have to first make sure that an entire row (or rows) is selected because this command can be used to delete columns and non-row/column ranges, as well as a bunch of other stuff. I make sure that what is selected is a Range and that the address of the range is the same as the entire row(s).

For mRowDelButton, I know that a whole row is selected, because you can't show that menu otherwise. The action in each procedure is a message box, but you'll likely need something more substantive. Don't forget that you can set the CancelDefault argument of these event procedures to True to prevent the deletion from happening.

If I missed a way that a user can delete a row, or you have a better method for detecting row deletions, please leave a comment.

I have a requirement to log all the changes made to a particular worksheet. I cringe whenever I'm faced with that particular task. I could use a Worksheet_Change event coupled with a Worksheet_SelectionChange event to log the old cell value and the new cell value. I really hate that option for a couple of reasons. First, I would have code running all the time which would be a drag on the system. Also, it logs way too much stuff. For instance, if a user changes a cell value from 1 to 2, then changes it back from 2 to 1, you have two log entries that don't add much to the log. It makes the log file messy and lessens its value in total. Another problem occurs when the user changes a whole slew of cells at once. Make a Worksheet_Change log procedure then delete the used range of the worksheet. With just a 10 column x 10 row worksheet, you've just made a 100 log entries with a noticeable lag in performance.

Speaking of changing a bunch of cells, I would have to save the old value in a module level variable using the SelectionChange event so that I could log it when the user fires the Change event. That wouldn't be such a big deal if I could use the Target variable from each of those event procedures, but when the user selects a range of cells, I have to save the "old value" from every cell in the selection. What a pain.

Normally, I would recommend that people don't log their changes. But I have situation that's hard to defend. There's a one worksheet workbook with a fairly limited UsedRange. It's an important table that a lot of other sheets will use and that many users will need to update as necessary. Everything happens (or should happen) in this limited UsedRange and there will never be any other sheets in the workbook. It's a prime candidate for change logging because many users can change the data and that data affects every other user. If things get screwy, there needs to be some accountability.

Nevertheless, I wasn't going to use a Worksheet_Change event. There's just too much to track and too many problems that can arise. So I settled on a different tack, which I (finally) describe here.

The basic structure is that I save a copy of the file when it's opened and I compare the current file with this saved copy to see what's changed. This has the benefit of keeping the log file fairly clean because the whole change/change back scenario isn't logged. Only the final changes against the original are logged. It also has the advantage that the code is running less. With a Change event, the code runs whenever you do absolutely anything. With this, it only runs on Open, on Save, and on Close (as described in a bit).

I'm not saying there aren't challenges with this because there are. At first, I decided that I would save a copy on Open and compare on Close. I'm sure the brighter of you have already figured out the problem with that. You can close a file without saving it. So now I have to determine if the user cancels the close, closes without saving, or closes and saves. That's all well and good, but I only have a Before_Close event and these user decisions happen after that fires. Ultimately, I had to settle on logging at every save. If my goal was to only log real changes, this takes me farther away from that goal. Now a user can change a cell, save, change it back, and save it again. It's still less than the alternative. Here's the basic structure:

When the file is opened, I use the SaveCopyAs method to save it in its current state, heretofore referred to as OldCopy
When the file is saved, I compare the two workbooks and log the changes. Then I close the OldCopy, delete it with a Kill statement, and resave the NewCopy so OldCopy is now current.
When the file is closed, I kill the OldCopy. Not so fast my friend, as Lee Corso might say. I had to control the hell out of the BeforeClose event for this to work. The long and the short of it is that I had to eliminate the Cancel option and program my own "Save and Close", "Don't Save and Close" options. Either way, that file was closing. Not ideal, but that's life.

Figuring out which cells changed posed another problem. Do I loop through the newer file and compare it to the older file or vice versa. Or do I compare all 65,000 rows x 256 columns to make sure I don't miss something. I don't think so. I ended up looping through the newer and comparing to the older, but I didn't necessarily use the UsedRange of the newer. I counted the cells in the UsedRange from both sheets and used the Address property of the larger of the two to define the corpus of cells to change. This, however is not foolproof.

The main issue with differences in the UsedRange was deleting rows and columns. If the user deleted half the rows in the newer file, then the newer file's UsedRange might be quite a bit smaller that the UsedRange of OldCopy. If I just loop through the UsedRange of NewCopy, then I'm not actually logging all the changes because I'm missing all the zeros in NewCopy that had values in OldCopy. Counting the cells gets close, but it's not perfect. If I delete a bunch of columns in NewCopy but add a bunch of rows, the UsedRange may have a higher count, but still not catch every change.

Now that I type this, I can see that I should count the rows and columns separately and use the greater of each independent count to determine which range I should compare. Okay, I'll change the code, but I don't guarantee that I won't screw it up.

I haven't commented this code very well (count of comments = 0) so you'll have to rely on the above description. I think the variables speak for themselves. Anything that starts with a 'g' is a global variable. Anything in all caps is a constant, proper case is a variable. The error handling scheme is straight out of Professional Excel Development. The variable gsIdentStandardsOpen holds the name of the person who opened. This global variable is set when the workbook is opened and a password is supplied. I'll describe the password scheme in a future post. I don't know if any of that helps, but there it is.

So there it is. I'm tired of typing, but if there's something I didn't explain be sure to let me know. You comments on this strategy, as always, are welcome and expected.