AutoInsert into Cell

[tobyhutton1234]

Hi There,

Does anyone know how can achive this.

When a user clicks on a cell within B5:B800 I want this to automatically appear: RTS2011/ and then after the / the user can input a max for 4 chars after.

Any suggestions would be great.

Regards

Toby

 

[Darren Bartrup]

Something like this pasted into the Sheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("$B$5:$B$800")) Is Nothing Then
        With Target
            If .Value = "" Then
                .Value = "RTS2011/"
                With .Validation
                    .Delete
                    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=AND(LEN(" & Target.Address & _
                        ")>8, LEN(" & Target.Address & ")<=12,LEFT(" & Target.Address & _
                        ",8)=""RTS2011/"")"
                    .IgnoreBlank = True
                End With
                Application.SendKeys "{F2}"
            End If
        End With
    End If
    
End Sub

Note - I'm sure there's a better way of editing a cell rather than use Application.SendKeys "{F2}", just couldn't think of it.