Formating a cell after successful validation

[Kardacian]

My code in post 7 gives me the result that I want. The user can type in any case that they want. If they enter the proper phrase the text will get formatted to the proper case and saved to the DB. However I dont like using CellFormatting.

The CellFormatting event fires often more so than I expected. I put a simple counter in and just the act of clicking a column causes it to fire. Which means the code will run even when the value of a cell has not changed. Since other columns will need similar work albeit simple I dont want code to run unnecessarily. ie if the contents haven't changed don't check the formatting.

The other thing I noticed is the CellFormatting event does not file until you complete your edit of the cell. So if the validation fails formatting does not occur which makes sense. But, if you escape out it will fire also.



As jmcilhinney has suggested, using the CellValidated event is a much better choice. The code only runs if the data has changed. Even better the Validated event does not fire if you start to edit the cell and then cancel out by hitting the Escape key (also makes sense). The load time of the grid is noticeably faster when the the CellFormatting event is not used. In my opinion avoid that event if you can or keep the code in it short as possible.

Using the CellValidated will be my go to solution to start.

I was not able to use the cell.Value.ToTitle statement. It repeatedly causes an exception error. I did add the extension code as outlined above. I tried a few different ways and it compiles, but I must be implementing it wrong. so for now I am using the StrConv

cell.Value = cell.Value.ToTitle() 'crashes
cell.Value = StrConv(cell.Value, vbPropperCase) 'works


An even better solution would be to use a combobox and I must admit I did not realize that is an option. Before I can go down that road I have to ensure that change to the layout is acceptable. Plus I have to figure out how to do it.