change sheet to Upper case

matthewh · May 14th, 2002, 10:29 AM

Mailing stuff worked thanks to earlier posts.

I need to set the sheet to upper case and then modify any changes to the sheet to upper case. I've tried Ucase in a

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Statement, but just get type mismatch errors, or it simply doesn't work. Can anyone help?

Matt

NateO · May 14th, 2002, 10:31 AM

Look at Al's post:

http://www.mrexcel.com/board/viewtop...8275&forum=2&2

NateO · May 14th, 2002, 10:44 AM

To clean up existing data:



Sub upr()

Dim cl As Range

For Each cl In ActiveSheet.UsedRange

If Not IsEmpty(cl) And Not cl.HasFormula Then _

cl.Value = UCase(cl.Value)

Next cl

End Sub

Put this in a normal module and run.
_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 09:44 ]

matthewh · May 15th, 2002, 01:58 AM

hI GUYS

OK. I tried the 'If Target <> "" Then Target = UCase(Target)' one, and the other one. Both worked, but I have some code to clear the form of all entered data, and one to save the data, print the form etc. Each time there's any change, especially on clearing out the form data, even deleting just entered data, I get a type mismatch on this line of code. The users won't like it, you know

Tom Schreiner · May 15th, 2002, 02:33 AM

Hi

Your code to clear...
Beginning of procedure:
Application.EnableEvents = False
this will disable the change event.
At the end of the same procedure re-enable your events like this:
Application.EnableEvents = True

A little added surety:
Change:
'If Target <> "" Then Target = UCase(Target)
To:
'If Target <> "" And Target <> Empty Then Target = UCase(Target)

Tom

[ This Message was edited by: TsTom on 2002-05-15 01:35 ]

May 14th, 2002, 10:31 AM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Look at Al's post: http://www.mrexcel.com/board/viewtop...8275&forum=2&2 __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

May 14th, 2002, 10:44 AM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	To clean up existing data: Sub upr() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not IsEmpty(cl) And Not cl.HasFormula Then _ cl.Value = UCase(cl.Value) Next cl End Sub Put this in a normal module and run. _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-05-14 09:44 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 10:29 AM	#1
matthewh New Member Join Date: Mar 2002 Posts: 34	Mailing stuff worked thanks to earlier posts. I need to set the sheet to upper case and then modify any changes to the sheet to upper case. I've tried Ucase in a Private Sub Worksheet_SelectionChange(ByVal Target As Range) Statement, but just get type mismatch errors, or it simply doesn't work. Can anyone help? Matt

May 15th, 2002, 01:58 AM	#4
matthewh New Member Join Date: Mar 2002 Posts: 34	hI GUYS OK. I tried the 'If Target <> "" Then Target = UCase(Target)' one, and the other one. Both worked, but I have some code to clear the form of all entered data, and one to save the data, print the form etc. Each time there's any change, especially on clearing out the form data, even deleting just entered data, I get a type mismatch on this line of code. The users won't like it, you know

May 15th, 2002, 02:33 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Your code to clear... Beginning of procedure: Application.EnableEvents = False this will disable the change event. At the end of the same procedure re-enable your events like this: Application.EnableEvents = True A little added surety: Change: 'If Target <> "" Then Target = UCase(Target) To: 'If Target <> "" And Target <> Empty Then Target = UCase(Target) Tom [ This Message was edited by: TsTom on 2002-05-15 01:35 ]