Using a trigger cell that will not truncate a number starting with zeros

[hshort]

Hi,

The situation that I am facing is that identifiers that are entered into a column are used to search a database and display data in other columns. However, when a number is entered that starts with a zero, excel automatically truncates the number to omit the zeros.

I am attempting to use a trigger so that once a number is entered into a cell and a different cell is selected, excel will 'force' the cell to remain the same, if that makes sense. I'm not sure if this would be accomplished by storing it as a text or another method, and I have very little experience with VBA so any input would be appreciated. The basic code that is in place right now is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox Target.Value
Application.EnableEvents = False
Target.Value = Target.Value & " please help"
Application.EnableEvents = True
End Sub

This was set up by another coworker and just adds "please help" to any cell that is changed. Is it possible to add a ' before the value in the cell so that the number starting with zero would be stored as text and remain the same?

 

[Snakehips]

hshort,

Welcome to the forum.

If purely identifiers then can you not just format relevant cells or whole column as Text?
Then delete the event code.

Hope that helps.

 

[hshort]

I don't think that will work because this will be part of an existing financial tool built in Excel that has restrictions including formatting of columns. I may be able to talk to the programmer and see if formatting would be possible.

Is there any way to do this using a trigger that would take the contents of the cell, which will be a variety of different types of identifiers, and store it as text? This might be by formatting the actual cell or adding the ' symbol before the identifier so that it is stored as text? I know that the & symbol will add certain text after the contents of the cell, is there a way to add something beforehand?
Thanks

 

[Snakehips]

hshort,

I'm struggling a little here, in part because I don't know the nature of your data etc.
My comments/ questions would be...

Is the identifier not just stored in one column?
Surely, whatever the type, the identifiers will be text strings not numeric values so why not format the column as text?
What is meant to be achieved by the existing code that adds " please help" to each and every data input?


Please forgive me if I'm missing something obvious.

 

[hshort]

The code that I pasted was just the starting point that my coworker set me up with, and told me to go from there. He was just demonstrating the functionality of a trigger in general and how to code it so that when a value in a cell is changed, it automatically triggers the change [in this case adding a phrase that I changed to "please help" for the purposes of this forum]. I realize now that this wasn't clear and confused the issue.

I plan on asking the person that built the tool if re-formatting the column is possible, but since he put me to this task I'm guessing that he would have considered that, but is most likely not possible for whatever reasons/restrictions are built into the tool. But yes, the identifiers will always be entered in the same column. Many of the identifiers are numeric values entered by a user, which is then compared to another database and the tool retrieves data and pastes it in separate columns. The only snag right now is the truncating of numbers that start with zero, which are then not recognized in the other database to obtain the other information

Hope that helps clear it up a bit. Thanks for your input

 

[GSKras]

Can you enter the value with a leading tick-mark (')? That forces "string" type (retaining leading zeroes), but basic math operations (+, -, *, /,...) still work, including using "*1" or "+0" or "--" to force it to become a number. The tick-mark is invisible, except in the formula bar.

 

[hshort]

Hi GSKras,

Yes that is essentially what I am trying to do, but to have it automatically happen when a value is entered. A lot of different users will be using the tool, and since we can't rely on them to remember to always enter the tick-mark ('), we need to find a way to build it into the code so that it automatically enters one before the contents of the cell when they move to a different cell.

 

[GSKras]

By any chance, is the entry always supposed to be the same number of characters? If so, custom number format (E.g. 00000) will force display of leading zeroes and/or you can process the contents of the cell elsewhere to pad-out the string. If, as it seems, you need it as a text string, are you sure it doesn't work to just apply the "text" format to the input cells?

 

[Snakehips]

hshort,

If an identifier that should have a leading zero is not being recognised because it has been truncated then surely that is because it is being compared as a string, not as a number. Hence my belief that the column that takes the identifiers should be format as text!!

I don't think there is anything to gain by custom formatting. Custom formatting will only change the way the number is displayed. 123 formatted as 0123 is still number 123 at the end of the day. Plus if you have different numbers of characters etc etc.!!!

You cannot code the Worksheet_Change event to add a leading ' as the input of say 0123 will be resolved to 123 before concatenating the '
So you will end up with '123 not '0123

The best 'fiddle' I can come up with that may suit your needs, is to rejct any input that resolves to a number. Message the user that he must prefix the identifier with '

Suggested code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 1 Then Exit Sub  'edit to suit entry column
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
MsgBox "Please re-enter this identifier prefixed by a single quote mark" ' "  "
Application.Undo
Application.EnableEvents = True
End If
End Sub

Hope that helps.

 

[hshort]

I ended up using this code, which accomplished what I was looking for, just so you know:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Set r = Range("A1:A1000") 'or wherever the data is being entered
If Not Intersect(r, Target) Is Nothing Then
r.NumberFormat = "@"
End If
End Sub

Thanks for all the input