Substitute chr(39)

  • Thread starter Thread starter Jos Vens
  • Start date Start date
J

Jos Vens

Hi,

I made function (see below) to substitute mistake input into right input

eg. when I type the letter O, I substitute in zero 0

I also like to let the laptop-user (AZERTY-keyboard layout) use the upper
row of the keyboard without shift pressed in to get figures so I substitute
& by 1, é by 2 etc.

Everything works fine, except for 4 because the ' sign (chr(39)) is used to
tell excel this is text. I cannot substitute ' in 4 if it is in the
beginning of my input (I works right for (' to make 54 but not '( in 45)

Can anyone help to substitute ' in 4 when it's in the beginning of my input?

Thanks,
Jos Vens

Here goes my code

Function SET_Value(vInput As Range)

Dim vSubstitute

vSubstitute = vInput
vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-")

vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3")

vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4")
'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '->does
not work either

vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9")
vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0")

vInput = vSubstitute

End Function
 
Jos,

Have you considered disabling the ' key like the following?

Application.OnKey ("'"), ""

Thus the user will only be able to enter ' preceded by any other character,
but then your...

vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4")

....will take care of it.

Regards,
KL
 
Hi,

even without disabling the "'" key, it works fine (equal as your
suggestion), only the substitute does not take place. I could use
Application.OnKey ("'"), "4", but here I prohibit the user by entering a ',
and that I cannot do.

Thanks anyway, more suggestions?
Jos
 
Jos,

Application.OnKey ("'"), "4" wouldn't work anyway.

The substitution ActiveCell = WorksheetFunction.Substitute(ActiveCell,
Chr(39), "4")
DOES work for me.

KL
 
Jos,

You may also want to consider adding the below line of code at the end of
your function. After all substitutions have been made there should only [']
be left and therefore your vSubstitute would be returning Text, not Number.
I guess you are quite safe doing this as the last step:

If WorksheetFunction.IsText(vSubstitute) Then _
vSubstitute = CDbl(4 & vSubstitute)

Regards,
KL
 
Hi,

this works fine if the first character is a ' (= 4), but the
substitute-function does not change the type, so if you type (' (=54), you
get 454, because the IsText function remains TRUE, even if the letters are
replaced with figures!

Thanks anyway, more suggestions?
Jos


KL said:
Jos,

You may also want to consider adding the below line of code at the end of
your function. After all substitutions have been made there should only
['] be left and therefore your vSubstitute would be returning Text, not
Number. I guess you are quite safe doing this as the last step:

If WorksheetFunction.IsText(vSubstitute) Then _
vSubstitute = CDbl(4 & vSubstitute)

Regards,
KL


KL said:
Jos,

Application.OnKey ("'"), "4" wouldn't work anyway.

The substitution ActiveCell = WorksheetFunction.Substitute(ActiveCell,
Chr(39), "4")
DOES work for me.

KL
Click to expand...
Click to expand...
 
Back
Top