VBA to add a character to a string?

[Russk68]

Hi All,

I have a dropdown list of about 30 lines. The dropdown list is in column N. Some examples of the format below.
2x25'
2x75'
2x100'
3x25'
3x50'
3x75'

At times, I need an "s" to appear at the end like this:
2x100's

So, instead of doubling the size of my dropdown list, I would like to select an option from the dropdown list, then double click in the cell to add the "s" and double click again to remove the "s"

Possible?

Thank you!

Russ

 

[mikerickson]

If the dropdown list is a Validation list, then you can put this in the sheet's code module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim valType As Long
    
    With Target
        On Error Resume Next
        valType = .Validation.Type
        On Error GoTo 0
        If valType <> 3 Then
            Beep
        ElseIf .Value Like "*'s" Then
            .Value = Left(.Value, Len(.Value) - 2)
            Cancel = True
        Else
            .Value = .Value & "'s"
            Cancel = True
        End If
        
    End With
End Sub

Use the dropdown to select the entry and then double-click on the cell to toggle the terminal 's

 

[rlv01]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim S As String

    S = Trim(CStr(Target.Value))
    If Not Application.Intersect(Me.Columns("N:N"), Target) Is Nothing And S <> "" Then
        If Right(UCase(S), 1) <> "S" Then
            Target.Value = S & "s"
        Else
            Target.Value = Left(S, Len(S) - 1)
        End If
        Application.SendKeys ("{ESC}")
    End If
End Sub

 

[Russk68]

Thank you very much!
Would you be able to help me out with 1 more thing? Can you tweek it so it only runs in column Q,Y,AG?

 

[rlv01]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim S As String

    S = Trim(CStr(Target.Value))

    If Not Application.Intersect(Application.Union(Me.Columns("Q:Q"), Me.Columns("Y:Y"), Me.Columns("AG:AG")), Target) Is Nothing And S <> "" Then
        If Right(UCase(S), 1) <> "S" Then
            Target.Value = S & "s"
        Else
            Target.Value = Left(S, Len(S) - 1)
        End If
        Application.SendKeys ("{ESC}")
    End If
End Sub