Setting Uppercase in VBA 2

[Navvy]

Hi, I'm trying to force Uppercase in Excel. I believe the function is UCase. Has anyone been able to do this before?

 

[Rougy]

Yes, pretty easy

If Ucase(recordset.fields(0).value) = Ucase (variable) then....

 

[Navvy]

I'm trying to do this when Excel opens, rather than just for certain conditions.

 

[Navvy]

Anyone know if this is possible, i.e. set uppercase when Excel opens? Thx

 

[xlbo]

Other than looping thru all teh cells and converting to uppercase, I think you're out of luck. IMHO, you wouldn't want to do that anyway unless you have a pretty small spreadsheet. AFAIK, there is no option to convert all text to uppercase
Geoff

 

[uberpudge]

As far as converting everything that already exists to uppercase, you would have to loop through each cell and convert it to uppercase.

New stuff typed in by the user, on the other hand, can be easily remedied with the following snippet in the ThisWorkbook code module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Information.TypeName(Target.Value) = "String" Then
Application.EnableEvents = False
Target.Value = Strings.UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

Hope this helps,
Pete

 

[DaleWatson123321]

Pete,

THANKS !!! - A good one to store in my "library".

====> STAR for you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[simoncpage]

Uberpudge,

How could I adjust this coding so that it search an entire sheet and converts it to lower case BUT with the first letter of each string being capital...i,e ...Capital???

Any ideas?

Simon

 

[Navvy]

Uberpudge, thanks for the reply. I still seem to be getting Object errors. Have I missed something?

 

[megnin]

Uberpudge,

I cut and pasted your snippet into a code module of my spreadsheet and it did nothing. What did I do wrong? Does it require modification? I just want new text entered in the worksheet to be uppercase.

Thanks,
David

 

[uberpudge]

Simon,

Try this:
Public Sub MakeEmUpperCase()
Application.EnableEvents = False

Dim rngCell As Range

For Each rngCell In Sheet1.Cells
If Information.TypeName(rngCell.Value) = "String" Then
rngCell.Value = Strings.UCase(Strings.Left(rngCell.Value, 1)) + _
Strings.LCase(Strings.Right(rngCell.Value, _
Strings.Len(rngCell.Value) - 1))
End If
Next

Application.EnableEvents = True
Set rngCell = Nothing
End Sub

Hope this helps,
Pete

 

[uberpudge]

Naavy,

What version of Excel are you using?
Which code module did you paste it into?
What error number are you getting?

Hope this helps,
Pete

 

[uberpudge]

David,

Which code module did you paste it into?

Hope this helps,
Pete

 

[Navvy]

Morning Pete,
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Information.TypeName(Target.Value) = "String" Then
Application.EnableEvents = False
Target.Value = Strings.UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

It gives me Run-time error 424 Object Required error.

I'm using Excel 97.

Thank you.

 

[simoncpage]

Uberpudge

Thanks I had already done a similiar thing I however used
.....
.....
For Each rngCell In Sheet1.USEDRANGE
.....
instead of
.....
For Each rngCell In Sheet1.Cells
.....
.....
This just speeds things up a bit

Simon

 

[uberpudge]

Naavy,

I also use Excel 97 so that shouldn't be the problem.

Error 424 (Object Required) basically means that an unqualified object is being referenced (i.e., Excel doesn't know what the object is.)

As long as you've pasted this into the Workbook's code module (typically named "ThisWorkbook" in the VB Editor), it should work with no problems.

The only other thing I can suggest is to go the the Workbook's code module, pick "Workbook" from the Object menu (top left drop-down in the code view window) and pick "SheetChange" in the Procedure menu (top right drop-down in the code view window) and make sure that the objects being passed to the procedure by Excel are the spelled the same as in the code snippet.

If you want, email the sheet to me:

peter.hopkins@convergys.com

and I'll try to get it working and send it back. If you have any sensitive data in the workbook, please replace it with sample data that meets the specified criteria of the sheet (but is NOT sensitive) before sending it to me.

Should you choose the latter option, please be aware that while I won't be back in the office (or on Tek-Tips for that matter) until 5/28, I will be happy to work on your workbook upon my return.
Hope this helps,
Pete

 

[Loomah]

While looking for something completely unrelated I stumbled accross this, which I thought might be of interest to everyone who has already contributed to this thread, particularly the bit about 'Proper Case'

http://www.cpearson.com/excel/case.htm

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[DaleWatson123321]

Loomah,

Another GOOD one. Thanks. ===> STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca