Excel 97 and a user form help needed

[alinka]

Hi,

I have Excel 97. I have a set of numbers that need to be converted. Currently, I use a set of formulas for the converstion:

1) A six digit number in A6 needs to be converted to a new number using these formulas:

first I need to separate each number
=MID($A6, 1, 1)
=MID($A6, 2, 1)
=MID($A6, 3, 1)
=MID($A6, 4, 1)
=MID($A6, 5, 1)
=MID($A6, 6, 1)

then I need take the sum of each digit and divide the sum by 10:
=(B7+C7+D7+E7+F7+G7)/10

then I need to take the digit before the decimal point:
=MID(H7, 3, 1)

Then I need to make sure that if the above formula returns nothing, we show 0
=IF(I7="",0,I7)

At last, the formula creates the new number by combining a prefix: AR00 with the original 6 digit number and adding the new calculated last digit affixed at the end which is calculated from the above formulas:
=CONCATENATE("AR00",A7,J7)

Here's what I need help with:

1) I need to figure out how to make all these formulas into one formula.

2) I also would like to know how I can instead create a form where a user can input an old 6 digit number, and get a new number shown to him.

Please let me know if you guys can help me. Thanks so much!

 

[Russell Hauf]

Hi alinka, welcome to the board!

How about this:

I've created a form, with 2 text boxes and one command button (I left the names as their defaults: TextBox1, TextBox2, CommandButton1). I set the Locked property of TextBox2 to True, so that the user can't enter or change any data in that text box. So basically the user will enter a 6-digit number in TextBox1, and the code will show up in TextBox2.

Here's the code behind my form. If you create a form with 2 text boxes and one command button, you can paste this code in and it should work (you can right-click on the form and select View Code; then delete anything that shows up and paste in the code below):

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> CommandButton1_Click()
<font color='#000080'>Dim</font> lngTemp <font color='#000080'>As</font> <font color='#000080'>Long</font>

<font color='#000080'>On</font> <font color='#000080'>Error</font> Resume <font color='#000080'>Next</font>

<font color='#008000'>' Make sure that a real number is entered in</font>
<font color='#008000'>' TextBox1. For example, if the user enters</font>
<font color='#008000'>' 6678B3, an error will occur when trying to</font>
<font color='#008000'>' assign it to a Long Integer. Also, if the</font>
<font color='#008000'>' user enters 444.33 (6 characters wide), it</font>
<font color='#008000'>' will also return an error.</font>
lngTemp = Len(CStr(CLng(TextBox1.Text)))

<font color='#000080'>If</font> Err <> 0 <font color='#000080'>Or</font> lngTemp <> 6 <font color='#000080'>Then</font>
<font color='#008000'>' The user has not entered a valid number</font>
MsgBox "You must enter a valid 6-digit number"
TextBox1.SetFocus
<font color='#000080'>Exit</font> <font color='#000080'>Sub</font>
<font color='#000080'>Else</font>
Err.Clear
<font color='#000080'>On</font> <font color='#000080'>Error</font> <font color='#000080'>GoTo</font> 0
TextBox2 = "AR00" & TextBox1.Text & LastChar(TextBox1.Text)
<font color='#000080'>End</font> <font color='#000080'>If</font>

<font color='#000080'>End</font> <font color='#000080'>Sub</font>


<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Function</font> LastChar(strText <font color='#000080'>As</font> String) <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>Dim</font> intI <font color='#000080'>As</font> <font color='#000080'>Integer</font>
<font color='#000080'>Dim</font> sngTotal <font color='#000080'>As</font> Single

<font color='#008000'>' Sum up the digits of the number</font>
<font color='#008000'>' (note that this does not need to take a 6-digit number,</font>
<font color='#008000'>' it can handle bigger or smaller numbers</font>
<font color='#000080'>For</font> intI = 1 To Len(strText)
sngTotal = sngTotal + Val(Mid(strText, intI, 1))
<font color='#000080'>Next</font> intI

<font color='#008000'>' The mod function returns the remainder - so it's</font>
<font color='#008000'>' the same thing as taking the digit after the</font>
<font color='#008000'>' decimal place - no need to divide by 10.</font>
LastChar = CStr(sngTotal Mod 10)

<font color='#000080'>End</font> <font color='#000080'>Function</font>

</font></pre>

I hope this helps - let me know if you have questions about the code or the form.

-rh

 

[alinka]

Thanks so much for responding and for such detailed instructions. You guys are absolutely amazing!!!!!!!!! I'm new at Excel. I am trying to follow your instructions in order to make a form in Excel 97, but I see no such option in the menu. How do I go about doing that?

Thanks.

 

[Russell Hauf]

To create a form, you need to go into the Visual Basic Editor (the VBE). To do this, you can either go to the Tools menu, then select Macros, then Visual Basic Editor. If you do this, you will also see that to the right of the words Visual Basic Editor, it says "Alt+F11". So you can hold down Alt and hit the F11 key to open the VBE. Then go to Insert - UserForm. Also look at the Help for how to insert text boxes and command buttons.

Hope this helps,

Russell