Case Changing in Excel 2000

[PeterMoran]

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

 

[xlbo]

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

 

[PeterMoran]

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

 

[xlbo]

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

 

[PeterMoran]

Hi Geoff,

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

Thanks for your input.

Peter Moran