Excel 97 and a user form help needed

alinka

Board Regular
Joined
Oct 30, 2002
Messages
70
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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

Board Regular
Joined
Oct 30, 2002
Messages
70
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top