TRICKY ONE FOR YOU CODEMASTERS!!!

  • Thread starter Thread starter Dean Knox
  • Start date Start date
D

Dean Knox

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)
 
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
Click to expand...

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
 
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
 
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
 
Back
Top