A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon entry
into a text box.

When entering a number eg 487654321 and pressing enter, it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it so
ending in 1 becomes -1, ending in 4 becomes -4 apart from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox

Dean,

Add this code

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim sTemp

With TextBox1
Select Case Left(.Text, 1)
Case 4:
.Text = "D/" & Right(.Text, Len(.Text) - 1)
Case 8:
.Text = "A/" & Right(.Text, Len(.Text) - 1)
Case Else:
.Text = Left(.Text, 1) & "/" & Right(.Text, Len(.Text) - 1)
End Select
Select Case Right(.Text, 1)
Case "*":
sTemp = "-10"
.Text = Left(.Text, Len(.Text) - 1) & sTemp
Case Else:
sTemp = Right(.Text, 1)
.Text = Left(.Text, Len(.Text) - 1) & "-" & sTemp
End Select
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dean Knox" wrote in message
...
A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon entry
into a text box.

When entering a number eg 487654321 and pressing enter, it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it so
ending in 1 becomes -1, ending in 4 becomes -4 apart from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox

On Tue, 25 Nov 2003 03:46:49 -0800, "Dean Knox"
wrote:

A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon entry
into a text box.

When entering a number eg 487654321 and pressing enter, it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it so
ending in 1 becomes -1, ending in 4 becomes -4 apart from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox

Maybe this will give you some ideas?

================
txt = InputBox("Input a Number: ")
first = Left(txt, 1)
last = Right(txt, 1)
txt = Mid(txt, 2, Len(txt) - 2)


Select Case first
Case Is = "4"
first = "D/"
Case Is = "8"
first = "A/"
Case Is = "7"
first = "7/"
End Select

If last = "*" Then last = "10"
last = "-" & last

MsgBox (first & txt & last)
=================


--ron

Hi

all the codemasters must be too busy :) (or put off by the
title :) )


I would have thought this is fairly straightforward using
a Select Case construct (see Help if need be), or a bit
messier, use an IF statement to manipulate a variable
representing the input.

It all seems quite straightforward so I must have
misunderstood the question???

hth

Tim




-----Original Message-----
A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon
entry
into a text box.

When entering a number eg 487654321 and pressing enter,
it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if
the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it
so
ending in 1 becomes -1, ending in 4 becomes -4 apart from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox
.

I'm not clear where your text box is, but let's assume
that the text is in MyText

' first digit
SELECT CASE Left(MyText,1)
CASE "4"
MyText = "D/" & MID(MyText,2)
CASE "8"
MyText = "A/" & MID(MyText,2)
CASE "7"
MyText = "7/" & MID(MyText,2)
CASE ELSE
END SELECT
' now for the last digit
LastLeter = RIGHT(MyText,1)
MyText = Left(MyText,LEN(MyText)-1)
If LastLetter = "*" Then
LastLetter = "10"
End If
MyText = MyText & "-" & LastLetter


' MyText now contains your amended text
If its from a cell, then use the sheet's Change event to
run the code
If its from a form, then you could use an OK button to
check these text boxes...

HTH

Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon
entry
into a text box.

When entering a number eg 487654321 and pressing enter,
it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if
the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it
so
ending in 1 becomes -1, ending in 4 becomes -4 apart
from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox
.

Dean

One more option:

Sub Tester()
'Leo Heuser, 25 Nov. 2003
Dim First As Long
Dim Last As Variant
Dim LenNT As Long
Dim NewText As String
Dim NewSequence As Variant
Dim OldText As String

NewSequence = Array(0, 1, 2, 3, "D/", 5, 6, "7/", "A/", 9)

OldText = Application.InputBox("enter number")

First = Left(OldText, 1)
Last = Right(OldText, 1)

NewText = Replace(OldText, First, NewSequence(First), 1, 1)

LenNT = Len(NewText)

If Last = "*" Then
NewText = Left(NewText, LenNT - 1) & "-10"
Else
NewText = Left(NewText, LenNT - 1) & "-" & Last
End If

End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.


"Dean Knox" skrev i en meddelelse
...
A Challenge for you all, I desperately need help.

I would like code that will manipulate a number upon entry
into a text box.

When entering a number eg 487654321 and pressing enter, it
checks the 1st digit, and depending on the digit replces
it with a number, i.e a 4 is a D/, a 8 is a A/. But if the
first digit is a 7 it keeps it as a 7/. Basically the
ability for me to define what the first digit will be
replaced with.

It also checks the last digit and puts a dash before it so
ending in 1 becomes -1, ending in 4 becomes -4 apart from
when it ends in * then I want it to become -10.

A few examples

4123456789 --- D/12345678-9
412345678* --- D/12345678-10
8876543210 --- A/87654321-0
7232323232 --- 7/23232323-2

Hope someone is bright enough to help

Thanks

Dean Knox