• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: convert text to Upper case after leaving the cell

inddon

Member
Hello There,

I have a code which convert the entered text into uppercase for given columns. When I select multiple rows or columns and clear/delete the contents it goes into a infinite loop, and seems it is hanged.

Below is the code. Could you please advice:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim aa As Integer
On Error Resume Next

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000")) Is Nothing Then
If .Value <> "" And _
Not IsNumeric(.cell) And _
Not IsDate(.cell) Then
For Each cell In Target
cell = UCase(cell)
Next
End If
End If

Application.EnableEvents = True
End Sub


Thanks & greetings
Don
 
Hello There,

I have a code which convert the entered text into uppercase for given columns. When I select multiple rows or columns and clear/delete the contents it goes into a infinite loop, and seems it is hanged.

Below is the code. Could you please advice:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim aa As Integer
On Error Resume Next

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000")) Is Nothing Then
If .Value <> "" And _
Not IsNumeric(.cell) And _
Not IsDate(.cell) Then
For Each cell In Target
cell = UCase(cell)
Next
End If
End If

Application.EnableEvents = True
End Sub


Thanks & greetings
Don
Hi,

The code you posted is incomplete so I started again. Try it this way. Note there's no need to check for blank cells, dates or numbers, the only thing you do need to check is if a formula was entered because if it was converting to upper case would turn it into a value.

P.S. I thinks the Mod's will scowl at you for not using code tags.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Application.Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000"))
If Not cell.HasFormula Then
  cell.Value = UCase(cell)
End If
Next
End If
Application.EnableEvents = True
End Sub
 
Hi,

The code you posted is incomplete so I started again. Try it this way. Note there's no need to check for blank cells, dates or numbers, the only thing you do need to check is if a formula was entered because if it was converting to upper case would turn it into a value.

P.S. I thinks the Mod's will scowl at you for not using code tags.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Application.Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Intersect(Target, Range("H6:H5000,K6:K5000,L6:O5000,Q6:R5000, Q6:R5000, U6:V5000, AF6:AF5000"))
If Not cell.HasFormula Then
  cell.Value = UCase(cell)
End If
Next
End If
Application.EnableEvents = True
End Sub



I think for me this is the fastest answered question. Thank you Mike. :)

I used the BB code editor in the post, and formatted the code before posting, it didn't work I can see that now. My apologies.
Could you let me know hoe to use code tags in the post?


Thanks
Don
 
To Post codes use the codes as

before code
([)code(])
after code
([)/code(])

Don't use this ()
Hope it clear
 
I think for me this is the fastest answered question. Thank you Mike. :)

I used the BB code editor in the post, and formatted the code before posting, it didn't work I can see that now. My apologies.
Could you let me know hoe to use code tags in the post?


Thanks
Don
Glad that worked and thanks for the feed back.

Have a look at the tip at the top window when posting for using code tags.
 
Back
Top