PDA

View Full Version : Solved: Force lower case



Rejje
05-31-2011, 02:33 PM
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.

Sub FontSizeAndLowerCase()

Application.ScreenUpdating = False
Application.Calculation = xlManual

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

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Chabu
05-31-2011, 03:11 PM
use the help function a bit rejje!

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

Rejje
05-31-2011, 04:40 PM
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
05-31-2011, 04:45 PM
Some things you can apply to a Range of cells, but LCase is one cell at a time


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


Paul

Rejje
05-31-2011, 11:00 PM
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
06-01-2011, 12:24 AM
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.

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

How can I make this work?

GTO
06-01-2011, 12:33 AM
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
06-01-2011, 01:19 AM
Result exactly what I was looking for! Thanks!

GTO
06-01-2011, 01:36 AM
Happy to help and you are most welcome :-)

Paul_Hossler
06-01-2011, 06:27 AM
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.



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


Paul

Rejje
06-01-2011, 10:00 AM
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
06-01-2011, 03:43 PM
Public Sub Worksheet_Change(ByVal Target As Range)


is an event handler

Paul

Rejje
06-01-2011, 04:42 PM
Public Sub Worksheet_Change(ByVal Target As Range)


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!