PDA

View Full Version : Adding prefix to cell in column as it is added



Zoobster
04-29-2017, 01:27 PM
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
04-29-2017, 03:39 PM
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
04-30-2017, 06:54 AM
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
05-01-2017, 07:34 AM
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
05-01-2017, 04:10 PM
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
05-02-2017, 04:38 AM
Hi Zoobster
Please don't quote whole posts in reply, only those lines relevant to your question
Regards
MD

Zoobster
05-02-2017, 06:51 AM
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
05-02-2017, 06:55 AM
Sub M_snb()
columns(10).numberformat="BCN @"
end sub

SamT
05-02-2017, 07:02 AM
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
05-02-2017, 07:24 AM
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