none
Macro/Function to Expand defined 'acronyms' RRS feed

  • Question

  • I am wondering if it is possible to create an Excel Macro of Function that looks for specific things typed in a cell and changes them immediately to a predetermined phrase.
    Examples :
    "CTH " becomes "Court Hearing "
    "LTOC " becomes "Letter to Opposing Council "
    "CW " becomes "Conference With "

    I know how to do it but I cannot figure out if there is an event trigger I can use. In VB.NET I would use the TextChanged event for a TEXTBox and multiple If-Then statements or a Select Case, etc 

        Private Sub TxtProgress_TextChanged(sender As Object, e As EventArgs) Handles TxtProgress.TextChanged
            If TxtProgress.Text = "LTOC " Then
                TxtProgress.Text = "Letter to Opposing Counsel "
                TxtProgress.SelectionStart = TxtProgress.TextLength
            End If
        End Sub

    Sunday, September 1, 2019 8:43 PM

Answers

  • To:  Devon
    Re:  making text changes

    For any future entries you can use the bulit-in Auto Correct option in Excel.
    Find it under the "Review" tab on the Ribbon.

    For existing entries you can use the "Find and Select | Replace" option found on the Ribbon on the Home (tab) | Editing (group).

    '---

    Free Excel Add-ins and Workbooks at MediaFire...
    (the Custom_Functions add-in has 19 new Excel functions)
    • Marked as answer by Devon_Nullman Monday, September 2, 2019 1:48 AM
    Sunday, September 1, 2019 11:08 PM

All replies

  • To:  Devon
    Re:  making text changes

    For any future entries you can use the bulit-in Auto Correct option in Excel.
    Find it under the "Review" tab on the Ribbon.

    For existing entries you can use the "Find and Select | Replace" option found on the Ribbon on the Home (tab) | Editing (group).

    '---

    Free Excel Add-ins and Workbooks at MediaFire...
    (the Custom_Functions add-in has 19 new Excel functions)
    • Marked as answer by Devon_Nullman Monday, September 2, 2019 1:48 AM
    Sunday, September 1, 2019 11:08 PM
  • Hi,

    If you want to realize it with Excel VBA, it will be like this.
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("A2:A100")) Is Nothing) Then
            Select Case Target.Value
                Case Is = "CTH"
                    Cells(Target.Row, 2).Value = "Court Hearing "
                Case Is = "LTOC"
                    Cells(Target.Row, 2).Value = "Letter to Opposing Council "
                Case Is = "CW"
                    Cells(Target.Row, 2).Value = "Conference With "
                Case Is = "CF"
                    Cells(Target.Row, 2).Value = "Confer "
            End Select
        End If
    End Sub


    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Sunday, September 1, 2019 11:52 PM
  • I added entries to the AutoCorrect list with the program that generates the spreadsheets, works great, thanks. Here's a snippet.

                Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
                If xlApp Is Nothing Then
                    MessageBox.Show("Excel is not properly installed!!")
                    Exit Sub
                End If
                xlApp.DisplayAlerts = False
                xlApp.AutoCorrect.AddReplacement("C*", "CONFERENCE WITH")
                xlApp.AutoCorrect.AddReplacement("NC*", "NON-CHARGEABLE TIME")
                xlApp.AutoCorrect.AddReplacement("CT*", "COURT HEARING")
                xlApp.AutoCorrect.AddReplacement("P*", "PREPARATION OF")

    I added the asterisk after to ensure it didn't happen unless intended.

    Monday, September 2, 2019 1:48 AM