Fine, we've all written error-handlers for that, and we can write informative error messages telling the user what's gone wrong, and what to do next.
And we all *do* do this... Right?
As useful bit of user interface work is to flash up the range with the bad data, changing the background colour to red, and back again, two or three times. It's an easy bit of code to write, until you run into ranges containing merged cells - which are a nuisance, but an occasional necessity - because the
range.interior.color property fails silently on merged cells.
We also have a second consideration: interior.color sometimes returns zero for cells with no interior colour. This is embarrassing when you've read the original colour as zero and 'restore' the range to that after flashing and ringing bells.
This is the code to flash a cell in red, three times (the default settings for 'WarningFlash'):
WarningFlash rngFileControl.Cells(iRow, iCol)And this is what the code looks like to flash the cell (or merged cells) behind a command button, twice, in a tasteful yellow colour:
WarningFlash rngFileControl.Rows.Worksheet.OLEObjects("cmdSelectFiles").TopLeftCell, &HFFFF, 2
Note that we select the top-left cell of the range: my code for merged ranges only works if you specify the whole of the merge, or the first cell in the merge. And yes, I often put COM control buttons in merged cells: a well-formatted button with a 16*16 icon is twice the height of a standard Excel row, and I prefer to have the control in a merged single cell because 'move and size with cells' works badly if the control spans more than one cell.
Coding Note: best-practice would be to save your favourite colours as constants, or to use the built-in VBA colour constant for yellow: VBA.ColorConstants.vbYellow
So, without further ado, the source for the WarningFlash subroutine:
Public Sub WarningFlash(FlashRange As Excel.Range, _And now for the difficult bit: I'm using 'sleep', because Application.Wait only works in whole seconds. That API declaration needs to work in all Windows versions, and in all versions of VBA:
Optional WarningColor As Long = &HFF, _
Optional FlashCount As Integer = 3) Dim lngPriorColor As Long Dim i As Integer Dim rng As Excel.Range ' This unwieldly syntax works on ranges of merged cells, if you specify the top left cell Set rng = FlashRange.Areas(1).Worksheet.Range(FlashRange.Areas(1).Address) lngPriorColor = rng.Interior.Color If lngPriorColor = 0 Then Exit Sub For i = 1 To FlashCount rng.Interior.Color = WarningColor Sleep 500 rng.Interior.Color = lngPriorColor Sleep 500 Next i End Sub
#If VBA7 And Win64 Then ' 64 bit Excel under 64-bit windows Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong) #ElseIf VBA7 Then ' 64 bit Excel in all environments Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #Else ' 32 bit Excel Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End IfLet me know how you get on: or, better still, post your own solutions.
