VBA: convert text to Upper case after leaving the cell

[inddon]

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

 

[Mike H..]

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.

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

 

[inddon]

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.

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

 

[sgmpatnaik]

To Post codes use the codes as

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

Don't use this ()
Hope it clear

 

[Mike H..]

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.