Adding prefix to cell in column as it is added

[Zoobster]

I have this code below that works well if i want the beginning of my cell to always start with "BCN" and to only put the prefix if i have something entered in the cell. But hat if my i dont always need it to say "BCN" most of the time it will. If i was to type "EX" i would like it to not put "BCN" in front of it. I would appreciate any help on this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   
Const prefix As String = "BCN"
If Target.Column = 10 Then
    Application.EnableEvents = False
    If Target.Value <> "" Then Target.Value = prefix & Target.Value
    Application.EnableEvents = True
End If

[Paul_Hossler]

I have this code below that works well if i want the beginning of my cell to always start with "BCN" and to only put the prefix if i have something entered in the cell. But hat if my i dont always need it to say "BCN" most of the time it will. If i was to type "EX" i would like it to not put "BCN" in front of it.

How do you tell it to add the BCN prefix or just use what's there?

Also, that code looks like it will add BCN every time the cell changes

1. Cell Empty
2. Enter "ABC"
3. Cell = "BNCABC
4. Edit the cell (F2) to BCNABCD
5. Cell = BCNBCNABCD

[SamT]

Const prefix1 As String = "BCN" 
    Const prefix2 As String = "EX" 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)      
If Target.Column = 10 Then AddBCN Target
End Sub
 
Private Sub AddBCN(Cel as Range)

'In descending probability order
If left(Cel, Len(prefix2) = prefix2 Then Exit Sub
If Cel = "" Then Exit Sub
If left(Cel, Len(prefix1)) = prefix1 Then Exit Sub

        Application.EnableEvents = False 
 Cel.Value = prefix1 & Cel.Value 
        Application.EnableEvents = True 
End  Sub

[Zoobster]

For this to work in excel, don't i have to have everything under Private Sub Worksheet? I couldn't get that to work.

[SamT]

This line above is missing a ")" after "prefix2"

    If Left(Cel, Len(prefix2)) = prefix2 Then Exit Sub

Constants and variables declared at the top of a code page are available to all procedures on that page. (and if declared as Public, are available to all procedures on all code pages.)

In worksheet code pages that have Event Subs, I prefer to put all Constants before the procedures because they will only be created the first time they are used, then they will stay in memory until the workbook is closed. Variables and constants declared inside procedures will only stay in memory until that procedure ends. This saves some CPU cycles and improves the efficiency.

It is my style of coding to keep Event subs so small that they can only choose which procedure needs to be run and keep all the "Real" work in other subs. Sure, today, you only need one thing to happen when a Change is made to the sheet, but tomorrow, you might need something different to happen when a change is made in a different column.

It is easy to overload a single procedure with so many different "work" sections that they interfere with each other and the procedure just can't run. Another distinct advantage is that by breaking the "Real Work" sections out, everything becomes much easier to devlope logic for and easier to troubleshoot.

PS: On further consideration of my code above, I would change both instances of "AddBCN" to "AddPrefix1" so that it better describes what is happening for a programmer who hasn't seen the code before, or a programmer who hasn't seen the code in long while and forgot everything he/she was thinking way back then.

[mdmackillop]

Hi Zoobster
Please don't quote whole posts in reply, only those lines relevant to your question
Regards
MD

[Zoobster]

If Target.Column = 10 Then AddBCN Target - (So change it here to prefix1)


Private Sub AddBCN(Cel As Range) -(Change it here to prefix1)

?

[snb]

Sub M_snb()
   columns(10).numberformat="BCN @"
end sub

[SamT]

If Target.Column = 10 Then AddBCN Target - (So change it here to prefix1)

Private Sub AddBCN(Cel As Range) -(Change it here to prefix1)

If Target.Column = 10 Then AddPrefix1 Target

Private Sub AddPrefix1(Cel As Range)

[Paul_Hossler]

In the code mode for the worksheet, I'd use something like this (assuming that I'm understanding correctly)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Const prefix As String = "BCN"
    
    If Target.Column <> 10 Then Exit Sub
    If Left(Target.Value, 2) = "EX" Then Exit Sub
    If Left(Target.Value, 3) = "BCN" Then Exit Sub
    If Len(Target.Value) = 0 Then Exit Sub
    
    Application.EnableEvents = False
    Target.Value = prefix & Target.Value
    Application.EnableEvents = True
End Sub