Solved: Force lower case

[Rejje]

Hi!

What does a code look like that will convert upper to lower case in a range? I want to add it to the macro below. Note: The range will be different from "ALL_DATA_KP" but I need it to be in the same sub.

[vba]Sub FontSizeAndLowerCase()

Application.ScreenUpdating = False
Application.Calculation = xlManual

Range("ALL_DATA_KP").Font.Size = 10

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub[/vba]

[Chabu]

use the help function a bit rejje!

UCASE() and LCASE() do what you might reasonably expect them to do.

[Rejje]

Yes - of course i know. But: I've tried LCASE() in many ways and can't get it to work. Still a beginner at this...

[Paul_Hossler]

Some things you can apply to a Range of cells, but LCase is one cell at a time

[VBA]
Sub FontSizeAndLowerCase()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual

Range("ALL_DATA_KP").Font.Size = 10

For Each rCell In Range("ALL_DATA_KP").Cells
rCell.Value = LCase(rCell.Value)
Next


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
[/VBA]

Paul

[Rejje]

Problem solved and thanks Paul!

Are there other occations than upper/lower case that this logic applies to?

I mean it wasn't obvious with my short experience of vba script that I would need to loop through the range of cells rather than just go ahead and try to make the code execute the command on the whole area in a one single event.

[Rejje]

I've now tried another approach as well that I really believe would be better for solving this issue (see below). Changing to "Worksheet_Change" I suppose I could get the sub to instantly change to lower case after altering a cell within the range.

[vba]Public Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Range("AREA_FORCE_LCASE") something... Then ' this is where I don't get it to work
Target = LCase(Target)
End If

Application.EnableEvents = True


End Sub[/vba]

How can I make this work?

[GTO]

Hi there,

Try:

Option Explicit
    
Public Sub Worksheet_Change(ByVal Target As Range)
     
    Application.EnableEvents = False
    
    If Not Application.Intersect(Target, Range("AREA_FORCE_LCASE")) Is Nothing _
    And Target.Count = 1 Then
    
        Target = LCase(Target)
    End If
    Application.EnableEvents = True
End Sub

Hope that helps,

Mark

[Rejje]

Result exactly what I was looking for! Thanks!

[GTO]

Happy to help and you are most welcome :-)

[Paul_Hossler]

Couple of follow on thoughts / suggestions

1. If you're using an event handler, I'd suggest remembering the status of ScreenUpdating and EnableEvents, and resetting when you're done. The reason is that you might have EnableEvents already = False when the event handler is called, and you explicitly reset it = True. This might cause problems to the rest of the code

2. If you were to paste in more than a single cell that overlaps your named range, the check 'Target.Count = 1' would not allow the multiple pasted/entered cells to be made lower case. I'd suggest still using the For Next loop to cover the possibility.


[vba]
Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim bEvent As Boolean, bScreen As Boolean

bScreen = Application.ScreenUpdating = False
bEvent = Application.EnableEvents = False

Application.ScreenUpdating = False
Application.EnableEvents = False


If Not Application.Intersect(Target, Range("AREA_FORCE_LCASE")) Is Nothing Then
For Each rCell In Application.Intersect(Target, Range("AREA_FORCE_LCASE")).Cells
rCell.Value = LCase(rCell.Value)
Next
End If

Application.EnableEvents = bEvent
Application.ScreenUpdating = bScreen
End Sub
[/vba]

Paul

[Rejje]

1. No - I do not use an event handler. I'm uncertain was it is and how to use it. I do use a lot of ScreenUpdating, Calculation and EnableEvents =True or False all the time in the seperate subs (mostly located in modules).

What are the benefits of using an event handler? What does it look like?

2. Yes, I did notice the paste flaud. Was first thing I tried. However in this particular occasion all the cells in range are separate and all around are locked not to allow changes (not possible to paste many cells). So: I understand the possible problem but here any of these solution will do the trick (except for pasting lower case into a cell that is of course).

Thanks for all the help I get here!

Rejje

[Paul_Hossler]

[VBA]
Public Sub Worksheet_Change(ByVal Target As Range)
[/VBA]

is an event handler

Paul

[Rejje]

[vba]
Public Sub Worksheet_Change(ByVal Target As Range)
[/vba]

is an event handler

Paul

Ok. Of course. Well in that case I both know what it is and have used it a lot. Just not familiar with all terms yet. Gotta admit: I am a vba newbie.

Thanks Paul!