Consulting

Results 1 to 8 of 8

Thread: Toggle Text Case

  1. #1

    Toggle Text Case

    I have the below code from an Excel macro that I would like to use in Word. Can someone help modify the code so that it will work?

    Sub ToggleTextCase()
    'PURPOSE: Toggle selected cell text values between Upper, Lower, & Proper cases
    'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
    
    
    Dim FirstCellValue As String
    Dim rng As Range
    Dim cell As Range
    
    
    'Optimize Code
      Application.ScreenUpdating = False
    
    
    'Was a Range Selected? (Error Handling)
      If TypeName(Selection) <> "Range" Then
        MsgBox "No cell range was selected!", vbCritical, "No Range Selected"
        Exit Sub
      End If
    
    
    'Remove cells with formulas or cells that are hidden from selection
      On Error Resume Next
        If Selection.Cells.Count > 1 Then
          Set rng = Selection.SpecialCells(xlCellTypeVisible)
          Set rng = rng.SpecialCells(xlConstants)
        Else
          Set rng = ActiveCell
        End If
    
    
    'Store Current Case Structure of first cell in selection
      FirstCellValue = rng.Cells(1, 1).Value
      
    'Apply Case Change (based on first cell's current case structure)
      If FirstCellValue = LCase(FirstCellValue) Then
        'If Lowercased, make Propercased
          For Each cell In rng.Cells
            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
          Next cell
        
      ElseIf FirstCellValue = UCase(FirstCellValue) Then
        'If Uppercased, make Lowercased
          For Each cell In rng.Cells
            cell.Value = LCase(cell.Value)
          Next cell
        
      Else
        'If Propercased, make Uppercased
          For Each cell In rng.Cells
            cell.Value = UCase(cell.Value)
          Next cell
        
      End If
    
    
    End Sub
    Any help would be GREATLY appreciated!!
    Last edited by Rob Conklin; 02-09-2021 at 02:20 PM. Reason: Needing this Excel macro to work in Word.

  2. #2
    VBAX Contributor
    Joined
    Jul 2020
    Location
    Sun Prairie
    Posts
    118
    Location
    Quote Originally Posted by Rob Conklin View Post
    I have the below code from an Excel macro that I would like to use in Word. Can someone help modify the code so that it will work?
    ***

    Any help would be GREATLY appreciated!!
    It would help if you would tell us the goal of your code. What is it you are trying to accomplish? Although both use vba, the object models of Excel and Word are different. Word has a much more versatile Replace function.

    I confess that I did not read through your code. My coding skills in Word are upper-level beginner to moderate. In Excel, they are novice.

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    To work in Word, any such macro would need to be re-written from scratch. Perhaps:
    Sub Demo()
    With Selection.Range
      Select Case .Case
        Case wdLowerCase
          .Case = wdUpperCase
        Case wdUpperCase
          .Case = wdTitleWord
        Case wdTitleWord
          .Case = wdTitleSentence
        Case wdTitleSentence
          .Case = wdLowerCase
      End Select
    End With
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    My apologies Chas, I thought it was implied when I said that I wanted to use the the Excel module in Word. I do a lot of copy/paste in both Word and Excel, and unfortunately the source data is NEVER grammatically correct. So I spend a lot of time having to fix the text case. I found the solution for my Excel documents, but I could not get it to work in Word, as you explained above, the object models were different. I will confess, I can code a little in Excel, but I have zero clue about doing it Word.

  5. #5
    That worked beautifully, Paul. Thank you VERY much! Do you happen to know where I can find other useful macros for Word (besides VBAExpress)?

  6. #6

  7. #7
    Thank you. One more question. How do I mark the solution as completed?

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Via the 'Thread Tools' dropdown at the top of the page.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •