Thomas Reid
asked on
Force Cell contents to Uppercase
I have been using the following code to force any text entered into excel to Uppercase:
This is working fine.
However, if I delete the contents of a cell I get a VBA
Run-time error '13':
Type mismatch
This error also appears if I paste into a cell.
Any ideas how I can get around that?
Thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = VBA.UCase(Target.Value)
End Sub
This is working fine.
However, if I delete the contents of a cell I get a VBA
Run-time error '13':
Type mismatch
This error also appears if I paste into a cell.
Any ideas how I can get around that?
Thanks in advance
What version of Excel are you using? It doesn't happen with me using Excel 2010.
On Error Resume next is not only the "easy" way, it is also the "lazy" way. I do not recommend it, as it teach bad practices (ignoring errors)., and believe me, bad practices are tough to loose when you become used to use them.
If you delete the content of the cell, its value becomes null or empty. So no surprise the UCase function fail as its argument must be a string (and strings do not support null value).
Concatenate the cell's value with an blank string, if the result is not a blank string, call UCase, else do nothing.
If you delete the content of the cell, its value becomes null or empty. So no surprise the UCase function fail as its argument must be a string (and strings do not support null value).
Concatenate the cell's value with an blank string, if the result is not a blank string, call UCase, else do nothing.
Private Sub Worksheet_Change(ByVal Target As Range)
If((Target.Value & vbNullString) <> vbNullString) Then
Target.Value = UCase(Target.Value)
End If
End Sub
ASKER
Thanks for the response guys.
Alan your code seems to work fine, however it seems to make the workbook run slow?
Martin I'm using Excel 2013
Fabrice I am still getting an the same error upon cell deletion or paste using your code.
Cheers
Alan your code seems to work fine, however it seems to make the workbook run slow?
Martin I'm using Excel 2013
Fabrice I am still getting an the same error upon cell deletion or paste using your code.
Cheers
Try without the last 'On Error Goto 0', but I don't see why that would make any significant difference to speed.
Alan.
Alan.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help Alan, but it was still making the workbook run slow.
Your last suggest code seemed to do the trick Fabrice!
Many thanks all!
Your last suggest code seemed to do the trick Fabrice!
Many thanks all!
This might be even faster as Excel is able to convert ranges to array (and vice versa), and VBA compute arrays faster than ranges:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Cells.Count > 1) Then
'// more than one cell
Dim data() As Variant
data = Target.value
Dim i As Long, j As Long
For i = LBound(data, 1) To UBound(data, 1)
For j = LBound(data, 2) To UBound(data, 2)
If ((data(i, j) & vbNullString) <> vbNullString) Then
data(i, j) = UCase(data(i, j))
End If
Next
Next
Target.value = data
Else
'// only one cell
If ((Target.value & vbNullString) <> vbNullString) Then
Target.value = UCase(Target.value)
End If
End If
End Sub
Side note: This might mess up with formulas.
ASKER
That code made the workbook even slower.
Would it help to know that only 1 cell will be getting edited at a time?
It seems as though when using any of the codes, after inputting text into the cell excel then lags as it calculates something in the background.
FYI, there are no formula in this workbook, just a form for recording some tasks
Would it help to know that only 1 cell will be getting edited at a time?
It seems as though when using any of the codes, after inputting text into the cell excel then lags as it calculates something in the background.
FYI, there are no formula in this workbook, just a form for recording some tasks
Try this
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = StrConv(Target.Text, vbProperCase)
End Sub
Would it help to know that only 1 cell will be getting edited at a time?In the case of a single cell, the value property retrieve a single value, wich can be implicitly converted to string.
But in the case of multiple cells, the value property retrieve an 2D array, wich isn't convertible to string.
That code made the workbook even slowerHow many cells are you moving / cut-pasting / copy-pasting / deleting ?
The whole worksheet ?
A bunch of rows ?
A bunch of columns ?
Ouch ! Our solutions arn't tailored to work with that many cells.
Open in new window
This does have the downside of also masking other errors, but in something so short and simple, that should not be a concern to obsess over.You don't really *need* the 'On Error Goto 0' at the end, since the full code finishes at that point, but if using in a more elaborate code section, then you would normally want to return error handling to 'standard' as soon as possible.
Alan.