Force Cell contents to Uppercase

I have been using the following code to force any text entered into excel to Uppercase:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = VBA.UCase(Target.Value)
End Sub

Open in new window


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
Thomas ReidMaintenance TechnicianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
The easiest option would be to insert an 'On Error Resume Next' in there, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.Value = VBA.UCase(Target.Value)
On Error Goto 0
End Sub

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.
Martin LissOlder than dirtCommented:
What version of Excel are you using? It doesn't happen with me using Excel 2010.
Fabrice LambertConsultingCommented:
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.
Private Sub Worksheet_Change(ByVal Target As Range)
    If((Target.Value & vbNullString) <> vbNullString) Then
        Target.Value = UCase(Target.Value)
    End If
End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Thomas ReidMaintenance TechnicianAuthor Commented:
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
AlanConsultantCommented:
Try without the last 'On Error Goto 0', but I don't see why that would make any significant difference to speed.

Alan.
Fabrice LambertConsultingCommented:
Fabrice I am still getting an the same error upon cell deletion or paste using your code.
Hmm, I reproduced an error while messing with more than one cell.

In that case, browse the cells collection of the range, and Apply the same logic:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Excel.Range
    For Each cell In Target.Cells
        If (cell.Value & vbNullString <> vbNullString) Then
            cell.Value = UCase(cell.Value)
        End If
    Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thomas ReidMaintenance TechnicianAuthor Commented:
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!
Fabrice LambertConsultingCommented:
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

Open in new window

Side note: This might mess up with formulas.
Thomas ReidMaintenance TechnicianAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Target.Value = StrConv(Target.Text, vbProperCase)
End Sub

Open in new window

Fabrice LambertConsultingCommented:
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 slower
How 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.