PDA

View Full Version : [SOLVED] Automatically change Lowercase to Uppercase



blackie
05-05-2005, 05:34 AM
Hi there all, I have just joined this site about ten minutes ago after looking on the internet for help for what I need and to no avail...

I have to let you know that I have just started reading up on VBA and using it about a month ago and only know what I have learnt form books... I am hoping someone out there can help but if not, who cares I have found help on other things that I have been trying so that;s great, Ill be coming back here more often...

what I am doing, is setting up a spreedsheet to keep track of students results and am trying to write some code so it sets a range (D9:K29 which I have named "Exer") so that if someone enters text into this range and it is lowercase it will automatically change to upper when they move to another cell.. If this is possiable.. The code below is as close as I have got nad it only chages case if you run it like a macro..

I am using Office for MAC 2004 as well..

Any help would be great, or even just a post to say hi...
Thanks:thumb:bow:



Sub Upper()
Dim Exer As Range
For Each cell In Range("Exer")
cell.Value = UCase(cell.Value)
Next cell
End Sub

Bob Phillips
05-05-2005, 06:37 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D9:K29")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module. To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.

blackie
05-05-2005, 06:52 AM
Thanks that works a treat...:thumb Thanks for the quick reply too...
Now i just have to search in the VBA help about the code you used so I uderstand it as well..

Cheers XLD

brettdj
05-05-2005, 06:56 AM
Given that you may be changing more than one cell its more robust to check the range of interest, ie



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range, c As Range
Set myrange = Intersect(Target, Range("D9:K29"))
If myrange Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In myrange
c.Value = UCase(c.Value)
Next
Application.EnableEvents = True
End Sub


Cheers

Dave

Bob Phillips
05-05-2005, 07:09 AM
Given that you may be changing more than one cell its more robust to check the range of interest, ie


I accept the point about multiple cells, as a paste could create a mult-cell target, but there is no need to do the whole range every time, just target.

Also, you make the fundamental mistake of no error checks, so if something goes wrong, you exit with events disabled, very bad for the poor user


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D9:K29")) Is Nothing Then
For Each cell In Target
cell.Value = UCase(cell.Value)
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

blackie
05-05-2005, 07:10 AM
Thanks again

Bob Phillips
05-05-2005, 07:11 AM
Blackie,

Do you want a quick summary of what is happening?

blackie
05-05-2005, 07:16 AM
Yeh sure.. That would be great

brettdj
05-05-2005, 07:31 AM
@ xld,

If you can point out an potential error in my code I'll stand corrected. A simple standalone Change event doesn't need an error handler. And you will alos notice that I only invoke the EnableEvents if a valid range exists rather than for each iteration as you do

As for your new code :

The updated version will format every cell in the target if there is any intersection with D9:K29 regardless of the target area. For example, the whole worksheet would be converted to upper case if you pasted text throughout it - if the user could bear the FOR loop that is. Now thats bad for the poor user

And you might as well tidy up that unused variable

Cheers

Dave

@ Blackie

Whenever a range (target) in this sheet is changed, the Change event fires

My code creates a new range that checks for an intersection with the target (the range this is changing) with D9:K29.

If this intersection doesn't exist, ie myrange is nothing, then the code exits. If the range does exist, EnableEvents is turned off as otherwise once the cells are updated to uppercase the code will fire itself again. The code loops through only those cells that are contained in both target And D9:K29, then makes them uppercase. Finally it restores the EnableEvents so that the code will fire next time a target is changed

Cheers

Dave

Bob Phillips
05-05-2005, 07:33 AM
Okay, this is it.

First thing to note is that when the Change event is fired, the changed cell(s) is passed to the event code as an argument, which we call Target (we could call it anything).


On Error Goto ws_exit:

simply setting an exit should an error occur so that we can control the errors


Application.EnableEvents = False

disable events to stop any changes we make here from firing further events


If Not Intersect(Target, Me.Range("D9:K29")) Is Nothing Then

The tricky bit! We are checking here whether the cell(s) being changed are part of the range D9:K29. The result of this check is a range object if true, so we can check for not nothing (it woruld be nothing if the cell(s) were not part of that range


For Each cell In Target

'Now we iterate through each cell within the cells being changed


cell.Value = UCase(cell.Value)
'and just upshift that cell


Next cell

'self explanatory


End If
'self-explanantory


ws_exit:

'a label to our error handler


Application.EnableEvents = True

'reset events so as not to mess up. This bit gets entered for an error or no error

End Sub

johnske
05-05-2005, 07:41 AM
Hi Blackie,

The code here by xld:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
Dim cell As Range
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D9:K29")) Is Nothing Then
For Each cell In Target
cell.Value = UCase(cell.Value)
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

can be shortened to this (the use of Value is redundant excel always assumes you're referring to value unless stated otherwise. Similarly, the use of Me is redundant here and by re-wording the On Error statement and re-arranging we can eliminate ws_exit as well):


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, [D9:K29]) Is Nothing Then
For Each cell In Target
cell = UCase(cell)
Next
End If
Application.EnableEvents = True
End Sub

regards,
John :)

brettdj
05-05-2005, 07:50 AM
Repeat, xlds code will not work as required. If any item in the range D29:k29 is changed then the entire target will be operated on.

The code should run only on the range of interest. Refer to my example.

Cheers

Dave

Bob Phillips
05-05-2005, 07:56 AM
the use of Value is redundant excel always assumes you're referring to value unless stated otherwise


This is so, but I think that good coding practice suggests otherwise. Also it doesn't work in .Net



Similarly, the use of Me is redundant here


This is also so, but IMO it is good practice because if the code is called from another sheet it will assume that sheet.



and by re-wording the On Error statement and re-arranging we can eliminate ws_exit as well)


Crash and burn techniques:rotlaugh: . On Error Resume Next is a sledgehammer to crack and nut compared to the simple elegant error handling I showed. By adding On Error Resum Next, you could have any number of errors just being ignored, causing untold damage


Repeat, xlds code will not work as required. If any item in the range D29:k29 is changed then the entire target will be operated on.


Not so, but there is a flaw in my logic if a copy/paste is done over the edge of D9:K29. My preferred way of handling that situation is to test for Target.Count > 1 and Msgbox and exit if so, I just quickly responded to your suggestion as I didn't agree with it :doh:. I don't think changing every cell is a good way at all, would you do it if there were 100000 cels in the range?

brettdj
05-05-2005, 08:03 AM
Not so, but there is a flaw in my logic if a copy/paste is done over the edge of D9:K29. My preferred way of handling that situation is to test for Target.Count > 1 and Msgbox and exit if so, I just quickly responded to your suggestion as I didn't agree with it :doh:. I don't think changing every cell is a good way at all, would you do it if there were 100000 cels in the range?

Ok, to be blunt

It is so. Any change that incoporates D9:K29 will run over the entire target.
Your preferred method of testing for one cell doesn't meet the user request. So why post it?
Yes I would change every cell in 100000 - if that is what the user needed. But I wouldn't use a FOR loop.
Cheers

Dave

johnske
05-05-2005, 08:05 AM
Crash and burn techniques:rotlaugh: . On Error Resume Next is a sledgehammer to crack and nut compared to the simple elegant error handling I showed. By adding On Error Resum Next, you could have any number of errors just being ignored, causing untold damage

Crash and burn techniques?? - You have On Error Goto ws_Exit, which leads to Application.EnableEvents = True and then End Sub.

I think if you look at what I wrote you'll find that it does exactly the same thing EXCEPT that the 'Resume' clears the error where-as your code doesn't...:thumb

Bob Phillips
05-05-2005, 08:08 AM
It is so. Any change that incoporates D9:K29 will run over the entire target.


Perhapos you want to explain exactly what you mean by that as I do not understand you, and shouting it doesn't help.



Your preferred method of testing for one cell doesn't meet the user request. So why post it?


Well that is odd as the OP posted back thanking me for the solution, and I quote '...Thanks that works a treat...'.

Need I say more?


Yes I would change every cell in 100000 - if that is what the user needed. But I wouldn't use a FOR loop.


Wow!

Bob Phillips
05-05-2005, 08:46 AM
Crash and burn techniques?? - You have On Error Goto ws_Exit, which leads to Application.EnableEvents = True and then End Sub.

I think if you look at what I wrote you'll find that it does exactly the same thing EXCEPT that the 'Resume' clears the error where-as your code doesn't...:thumb

I think I explained why I thought it was a bad practice, and just because it works in one instance does not suddenly make it a good practice.

Ignoring errors is bad, very bad (except where you want to test that there was an error and action accordingly, but that would be a very temporary action), and you should not be suggesting otherwise to less experienced users who might think that it is good.

johnske
05-05-2005, 09:14 AM
I think I explained why I thought it was a bad practice, and just because it works in one instance does not suddenly make it a good practice.

Ignoring errors is bad, very bad (except where you want to test that there was an error and action accordingly, but that would be a very temporary action), and you should not be suggesting otherwise to less experienced users who might think that it is good.

xld, I really dunno what you're going on about - Who is ignoring errors? As I said, the sequence of execution is exactly the same as the code you gave. The On Error Resume Next precedes an If-Then statement... If there is an error in there, execution then resumes with Application.EnableEvents = True and goes on to End Sub... That does not differ from what you have - Maybe you don't understand the On Error Resume Next statement. Perhaps a little help from the VBA Help files is in order here:

Resume Next If the error occurred in the same procedure as the error handler, execution resumes with the statement immediately following the statement that caused the error.

Bob Phillips
05-05-2005, 09:56 AM
Resume Next If the error occurred in the same procedure as the error handler, execution resumes with the statement immediately following the statement that caused the error.

That statement means that the error is ignored, it is not handled, but you pass on to the next statement as you yourself say.

Zack Barresse
05-05-2005, 09:59 AM
Resume Next can be used effectively. This is well known. Generally it's shy'd away from, but can be advantageous, as other methods can. Is one better than the other? Let me answer a question with a question: Does it matter, and do they both get the job done?

I'm more inclined to agree with xld in regards to the redundant assumptions Excel offers. Sure .Value is assumed, just as is the activesheet. Does this mean we should drop them? No. By far no. Although good code is not measured by this; it's only etiquette.

John: I don't see any difference in your code. You're splitting hairs here.

Dave:
And you will alos notice that I only invoke the EnableEvents if a valid range exists rather than for each iteration as you do.
Did I miss something? Prior to this post of yours, I didn't see this, in fact I saw the opposite. Might a post have been edited? :dunno

Everybody: The biggest difference in code is Dave's lines here:

Set myrange = Intersect(Target, Range("D9:K29"))
'...
For Each c In myrange
This is what sets everything apart. Basically you ONLY loop through this range, and not (potentially) the entire sheet, if all cell are selected.

I'll consider this thread Solved and it will be marked as such.


Take care everybody!! :yes

brettdj
05-05-2005, 04:54 PM
Thanks Zack for neatly closing this question off and pointing out the substantial difference. This one got a little hotter than it should have.

I posted my comments as I saw that the answer that blackie was happy with worked fine for a one cell change but would fail for a multi-cell change such as a range copy and paste.

Cheers

Dave