Add backslashes to text in cell before single quote

[gsgriffin]

It's been a long time since I've been in VBA. Working on a page that creates SQL code for me. Because of that, I would like to have the cells with field values I'm inputting automatically add backslashes before single quotes.

I've worked on some code that does a simple replace of ' with \', but Excel keep adding backslashes (I assume because after it finds and replaces the single quote, it looks to the next character and finds a single quote again (because the insert of the backslash moved the character.

I would like to have a onchange function that looks at the cell value (which is text) and changes text like:
"This isn't what I want"
to:
"This isn\'t what I want"

Editing a line and having change should not add a second backslash. Only one backslash. If one exists, don't add.

Any ideas on an elegant way to do this as opposed to taking the string, going through it character by character and inserting manually. Couldn't find anything anywhere, but I could be searching the wrong terms.

 

[gsgriffin]

Well, this is what I've come up with. I think the problem I was having was that the EnableEvents needed to be turned off or else the event kept being triggered over and over. I've added a nice touch of removing the backslash when entering a cell so the text editing is clean and normal. When exiting, the backslashes are added back in. Hope this helps someone.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 'Turn off the change event so it isn't triggered by the following
   Application.EnableEvents = False
        'By removing the backslash when entering a cell, I can edit plain text without backslashes to confuse the view
        Target.Replace What:="\'", Replacement:="'", LookAt:=xlPart, _
            MatchCase:=False
     'Turn back on the triggering of events
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    'Turn off the change event so it isn't triggered by the following
    Application.EnableEvents = False
    'Replace the values I want
    Target.Replace What:="'", Replacement:="\'", LookAt:=xlPart, _
         MatchCase:=False
    'Remove doubled backslashes...you may want them and need to remove the following...I don't want them
    Target.Replace What:="\\", Replacement:="\", LookAt:=xlPart, _
     MatchCase:=False
    'Turn back on the triggering of events
    Application.EnableEvents = True
End Sub

 

[gsgriffin]

Don't you hate it when the only posts on a thread are the person who asked the question in the first place. Bugs me!

After using my code, I realize that the lostfocus event does not have an opposite. Made it work by adding a public variable and call the function when selectionchange occurs on the last cell that had focus. In a sense, it is a GotFocus feature.

Public prev As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    'Turn off the change event so it isn't triggered by the following
    Application.EnableEvents = False
    'Replace the values I want
    Target.Replace What:="'", Replacement:="\'", LookAt:=xlPart, _
         MatchCase:=False
    'Remove doubled backslashes...you may want them and need to remove the following...I don't want them
    Target.Replace What:="\\", Replacement:="\", LookAt:=xlPart, _
     MatchCase:=False
    'Turn back on the triggering of events
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_LostFocus(ByVal Target As Range)
    'Turn off the change event so it isn't triggered by the following
    Application.EnableEvents = False
    'Replace the values I want
    Target.Replace What:="'", Replacement:="\'", LookAt:=xlPart, _
         MatchCase:=False
    'Remove doubled backslashes...you may want them and need to remove the following...I don't want them
    Target.Replace What:="\\", Replacement:="\", LookAt:=xlPart, _
     MatchCase:=False
    'Turn back on the triggering of events
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 'call worksheet change on previous cell.  This is needed if someone just gets focus but does not select a cell
 'unfortunately, there is no GotFocus event in Excel 2007. This does the same
 If Not prev Is Nothing Then
    Call Worksheet_Change(prev)
End If
 'Turn off the change event so it isn't triggered by the following
   Application.EnableEvents = False
        'By removing the backslash when entering a cell, I can edit plain text without backslashes to confuse the view
        Target.Replace What:="\'", Replacement:="'", LookAt:=xlPart, _
            MatchCase:=False
     'Turn back on the triggering of events
    Application.EnableEvents = True
    Set prev = Target
End Sub

 

[SkipVought]

Why not simply in a post process to all the sheet activity.

1. replace \' with '
2. replace ' with \'

Plain vanilla.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]