0

I am writing a program in the Worksheet_SelectionChange event to better understand the worksheet events, how it is fired and such.

My program takes the string in "A1" and gives the reversed string in "B1".

It is not as elegant as the Google Translate. In Google Translate with every keystroke, the translate function is up and running, while in my case I need to select a different cell then select "A1" again, for the event to fire and give the output.

Are there ways to make the events better or, create our own events, or use a combination of available events.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MyRange As Range
    Set MyRange = Sheet1.Range("A1")
    If Not Intersect(Target, MyRange) Is Nothing Then
        If Target.Cells.Count > 1 Then
            Exit Sub
        Else
            Target.Offset(0, 1).value = ""
            Target.Offset(0, 1).value = ReverseWord(Target.value)
        End If
    End If
End Sub


Private Function ReverseWord(val As String)
    Dim newstring As String
    Dim pos As String
    For j = Len(val) To 1 Step -1
        pos = Mid(val, j, 1)
        newstring = newstring & pos
    Next
    ReverseWord = newstring
End Function

enter image description here

Community
  • 1
  • 1
Charlie
  • 175
  • 8
  • 1
    The Change event will fire when the cell value is changed, so would be better here - but note that it is not triggered with every key stroke (VBA won't run while you are editing a cell) but only once the final cell value is confirmed. – Rory Dec 22 '21 at 10:25
  • Okay, I tried that and it is much better than `Worksheet_SelectionChange` . Say is there any room for further improvement ? – Charlie Dec 22 '21 at 10:30
  • 3
    There is no build in OnKeystroke event in Excel. Found this question https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell/11154285#11154285 but haven't tested it - and maybe it's a little bit to complicated if you are just playing around. – FunThomas Dec 22 '21 at 10:30
  • Thanks for the link @FunThomas Lemme see if whatever is in that, my cup of tea ? – Charlie Dec 22 '21 at 10:32

0 Answers0