I’ve been working on a quality control Excel workbook for our project schedules.
The workbook finds each schedule on the server, performs some quality tests, records the data, pivots the results and reports. Nothing new there. Yes, it works very well, thanks for asking.
But, while thinking about the structure of the data tables to hold the results of the quality tests, I realised that not all the defects found by the tests might be deemed defective by the Project Manager, and some constructive debate would ensue. I’m happy to be convinced that a particular test outcome is by design and not inherently defective, but how is that exception recorded so that identical defects don’t recur in future tests?
The answer is to include a column in the defects table to note that the unique task ID on this test for that plan is not defective, and so to ignore it for evermore. A simple yes or no option in the column’s data validation rules means that defects can now be reviewed and reversed, and an adjustment to the test code enables the defect to be included or not when the test is rerun. The testing code now inserts a ‘yes’ as its default value for this field ready for it to be overruled if necessary.
So far, so underwhelming. But, what if there are many defects that need to be reviewed this way? This would be quite possible with a large number of project schedules and I’d have to select ‘no’ a significant number of times when reviewing the results with the PM. Not a big deal, you might say, and I could just quick-fingeredly copy and paste. But still a time-consuming annoyance that I can do without. To make these selections more quickly, I wondered if including ActiveX check boxes for each data row might work; but I instantly rejected that as idiot idea number one for the day. The correct answer is to use a mouse double-click.
Double-click in the cell once, yes. Double-click again, no. Repeat and move on. Here’s how to do it.
This is the table column with its data validation drop down menu.
Just select yes or no. But, as noted above, that’s time-consuming so we’ll code around the irritation by activating the Worksheet_BeforeDoubleClick event.
Firstly, though, we need to test if the active cell is in the required table column because we don’t want the event to be triggered if any other cell is double-clicked. I’ve adapted code from here to do this and derived the IsActiveCellInTableColumn function, below.
Function IsActiveCellInTableColumn(theTable As String, _
theColumn As String) As Boolean
'This function returns true if the active cell is in
'a specified table column, false otherwise
If Not Intersect(ActiveCell, ActiveSheet.Range(theTable _
& "[" & theColumn & "]")) Is Nothing Then
IsActiveCellInTableColumn = True
Else
IsActiveCellInTableColumn = False
End If
End Function
How it works 1
- The function is defined as ‘Boolean’ so it will return ‘True’ or ‘False’.
- The table and column names are passed to the function so it knows where to do its stuff.
- The function checks if the active cell intersects with the range given to it. There’s some more information on the Intersect method here.
- The test is for ‘If Not Something Is Nothing’. In this context VBA doesn’t have a structure like ‘If Something’ because that’s too vague. So testing against a logical false makes very good sense. This might explain further.
Copy this function into a module in the VBA IDE.
Now, to test if the cell has been double clicked we need to insert some code in the Worksheet_BeforeDoubleClick event.
Double-click the worksheet name in the Project Explorer and select this event for the worksheet, shown below.
Now put the code shown below in this event, go back to the worksheet and start double-clicking as much as you can reasonably bear.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'Test if the active cell is in the required table column
If IsActiveCellInTableColumn("tblDefects", _
"Include In Future Tests") Then
'Test for the active cell's contents
With Target
Select Case .Value
Case "Yes"
.Value = "No"
Case "No"
.Value = "Yes"
Case Else
.Value = "No"
End Select
End With
Cancel = True
End If
End Sub
How it works 2
- The IsActiveCellInTableColumn function tests if the active cell is in the correct table column using the table name and column name.
- If it isn’t, nothing else happens and we can all go home.
- If it is, the event examines the active cell’s contents and changes the cell’s value from ‘No’ to ‘Yes’ and vice versa.
- If the cell is anything but ‘Yes” or “No” (it might have been deleted somehow) the function changes the contents to ‘No’. This could be ‘Yes’ if you wanted.
- ‘Cancel’ is set to ‘True’ so that the rest of the double-click event doesn’t happen and the cell edit is cancelled.
Things you can do
- Change the code to include any choices you want.
- Link cells’ data validation to a named range or table to get cyclable selections. Use a limited number of selections: maybe, 3 or 4.
- Consider wrapping the entire code in a function so it is reusable anywhere.
- Use as a binary true or false selection with Target.Value = Not Target.Value.
Example download
Lessons learned
- Good use of functions simplifies your life.
- There are too many double-clicks in this post.
- Do not use ActiveX controls in their gazillions on a worksheet.
- Using this functionality saves me many microseconds each day.
- Irritation and Annoyance each have two virtual .38s in their metaphorical heads.
Further reading
Over to you
Please leave a comment if you can think of any other uses for this functionality or improvements to the code. I am not a professional programmer and only do it to save me time in the long run. Or, occasionally, just for laughs. One day I might tell you about the time I tried to use Monte Carlo simulation to beat a Fantasy Football league using Visual Basic 6. It was doomed to failure from the kick off.
Footnotes
- ¹ Not recommended for every day use
- ² Recommended