Change to upper case without changing the font format

[praveenpatel421983]

Hi all,

I working on a spreadsheet which contains columns with strings. These strings are entered by different users. I need these columns to be in upper case always due to some reasons. But when I use code to change it, everything turns into uppercase but format of text is gone.
eg: "This item is used in Product X" should become "THIS ITEM IS USED IN PRODUCT X" instead it becomes "THIS ITEM IS USED IN PRODUCT X". Words which are made bold with increased font size is not followed.

I went to many posts but couldn't find the solution. I used below code. Please let me know what to change in this

Private Sub Worksheet_Activate()
    Range("A1:AF300") = [index(upper(A1:AF300),)]
End Sub

I also used this. this also didn't work

Private Sub Worksheet_Activate()   
   For Each x In Range("A1:AF300")
      x.Value = UCase(x.Value)
   Next
End Sub

Please help

Thanks in advance!
Praveen

 

[praveenpatel421983]

This might take long time as the range is very big in my case.
If the string is more than 255 can we split the string for every 255 save in variable or other temporary cell in the sheet and concatenate and put the result in the cell. Can this be done?

Sorry for the trouble again

 

[Yongle]

If the string is more than 255 can we split the string for every 255 save in variable or other temporary cell in the sheet and concatenate and put the result in the cell. Can this be done?

When I get an opportunity, I will test your suggestion BUT concatenating happens at TEXT level - I would expect Excel to format each character the same within each cell.

This might take long time as the range is very big in my case

1. How long does it take?

2. Have you considered limiting what can be input into a cell to 255 characters?

 

[pgc01]

Hi

I don't know the specifics of your problem and so this may not make sense your case, but a very simple way of achieving the result is to copy the cells to Word, capitalise and copy back to Excel.

 

[Yongle]

@pgc01 sounds like it could work
What is the code to carry out these steps whilst retaining all formatting?

1 Open Word from Excel
2 Copy cell
3 Paste to Word
4 Convert active text (in Word) to upper case
5 Cut and paste that text back to Excel cell

 

[pgc01]

Hi

I did a quick test and worked great.

I wrote some text in A1:A10, some of it bold or underline, different sizes, etc.

The code writes the capitalised text in the cels to the right, B1:B10.

I tested in excel 2013, windows 10.

- opens word and adds an empty document
- copies A1:A10 to the new document
- capitalises the text
- copies the text to excel to B1:B10
- closes the word document and closes word

This is what I used:

Remark: set the reference to the microsoft word object library

Sub CapitaliseCells()
Dim r As Range
Dim wApp As Word.Application
Dim wDoc As Word.Document

    Set r = Range("A1:A10")

    ' opens a new document in word
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Add(DocumentType:=wdNewBlankDocument)

    wApp.Visible = True ' for testing

    With wDoc.Range
        r.Copy
        .PasteExcelTable False, False, False
        Application.CutCopyMode = False
        .Case = wdUpperCase
        .Copy
    End With

    r.Offset(, 1).Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

    wDoc.Close SaveChanges:=wdDoNotSaveChanges
    wApp.Quit
End Sub

 

[pgc01]

P. S.
Some of the cells in A1:A10 have, of course, text > 255 characters.
The point is to work around the 255 limitation of the .Characters property of the excel Range object.

 

[Yongle]

@pgc01
I tested your code (post#15) and it worked prefectly - thanks for a timely intervention

@praveenpatel421983
Here are a few comments to help you test the code

1. Set the reference to the Microsoft Word Object Library
The code will not run without following these steps ...
- in VBA click on Tools \ References \ scroll dwn to Microsoft Word Object Library \ check the box \ OK

2. The code pastes the results in the adjacent column
- if there is something in that column either insert a column before running the code or amend this line

 r.Offset(, 1).Select

3. Also test to see what happens if there is a LOT of data
- I tested with 2000 rows (no problem, fairly quick)
- it is better to find out during testing if there is a problem as your data increases
- when testing I test with (minimum) 10 times the amount of data I expect
- if you find that the code is taking a very long time, then consider splitting the range and run the code several times
- I do not think you will have an issue unless your real data comprises thousands of rows and more columns

 

[pgc01]

I'm glad it helped.
I hope that this is only a temporary workaround and that ms fixes the problem with the .Characters property.

 

[Yongle]

I hope that ... ms fixes the problem with the .Characters property

do not hold your breath waiting for that wish to be fulfilled