Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case Changing in Excel 2000

Status
Not open for further replies.
Jun 5, 2002
417
AU
Hi,

I have the following macro for changing case in Excel which works fine until I recently tried to use it when I was in Edit mode in a particular cell - nothing happened. Is it possible to modify this macro to be able to be used when in Edit Mode?

Sub CaseChanger()
ShiftCase = ShiftCase + 1
If ShiftCase > 3 Then ShiftCase = 1
For Each Item In Selection
Select Case ShiftCase
Case 1
If Item.HasFormula = False Then
Item.Value = LCase(Item.Value)
End If
Case 2
If Item.HasFormula = False Then
Item.Value = UCase(Item.Value)
End If
Case 3
If Item.HasFormula = False Then
Item.Formula = Application.Proper(Item)
End If
End Select
Next Item
End Sub

Thanks
Peter Moran
 
nope - nothing can run if you are in edit mode in a cell. Data is only entered once the edit mode has been exited so until that time, excel does not recognise that there is anything in the cell

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Hi Geoff,

Thanks for your reply.

I note that in Edit Mode I can select Format Font and apply any property in this window to the selected component of the text in the cell. It would seem that changing case is similar to the other properties in the Format Font window.

Any further thoughts?

Regards,
Peter Moran
 
actually it isn't. The font is a property of the text. Upper or lower case is not a property of the text - lower case letters have different char values than upper case letters - they are in effect different text.

Excel recognises "lowercase" as different text to "LOWERCASE" so to change the case, you must change the text. As you are in edit mode, excel has not entered the text to the cell and therefore you cannot change the text.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Hi Geoff,

Good Thinking! These things appear so obvious when someone points them out!

Thanks for your input.

Peter Moran
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top