Form Design

wcthrill

Board Regular
Joined
Nov 28, 2005
Messages
80
Id like to have a form with the following info.

Date
Customer
Amnt (this may be broken)
category ( I will need to have multiple desks to add up to the total amnt)

for example

Cust ABC will have a total dep of $4000
The $4000 may be broken up into 2 categories
Can I have a form that I can enter all the info once. Or will I need to add multiple entries for that 1 deposit?

For ex.
1 entry for $1500 catergory fruit
1 entry $2500 category bread

Or can I just input the info 1 time on the form

hope Im making sense here
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
That makes perfect sense:) The answer is "How much work are you willing to invest?" If you wanted to make it really pretty, you could do this:
_________________
|_______||_______|
_________________
|_______||_______|
________________
|****************|
|****************|
|****************|
|________________|

The first two boxes would be text boxes that are for date and customer name. The second two would be a text box for amount and then a dropdown box (or text box) for category. Finally the bottom item would be a list box. You would then want two buttons "Add Amount" and "Add Record" The "Add Amount button would move the information in Your amount and Category fields to you list box. You could then just append as many items as you want before you click "Add Record". That way it is all visually available to user. Suggested buttons might be "Remove most recent amount" and "Cancel"
 

Razzle0147

New Member
Joined
Dec 30, 2005
Messages
4
wcthrill...

It makes sense on what you want it to do, but the problem I see here is that you are going to have to enter multiple entries, otherwise the program will not know when you want to switch categories. I could set it up where the only thing that you have to change is categories. Describe a little bit more on what your requirments are, just in case I missed something.
 

wcthrill

Board Regular
Joined
Nov 28, 2005
Messages
80
Im not a VB guy..so Ill need to know if this involves programming which it seems it does.

Razzle

Id like the form to look like this

[date]
[Amnt]
[Cust]
[category]

as Dorang says Ill need to add a drop down to sel the category.
am i better off doing a PT?
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

Not sure what you mean by PT.
But the programming is really easy; it's just a matter of knowing the right commands. Really all you need to know is ".AddItem" to control the ListBox/ComboBox and the basic array commands. (In this case ReDim and Ubound.) Here is some example code:

To use this you will need to first build a form following the general layout I put forth above. To save you work I made the code use default names. You will want 3 TextBoxes (TextBox1 will be customer, Textbox2 = Date, & TextBox3 = Amount); 1 ComboBox for "Category" (Please Type the values you want to be displayed in the category dropdown box in Sheet2); 1 ListBox to Keep track of multiple amounts, and lastly 2 command buttons (CommandButton1 will be the "Add Amount", CommandButton2 will be Add Record).
Right-click on the UserForm, select "View Code" and paste in the code below:
<hr>
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
<SPAN style="color:#007F00">'If you would like to put your categories in a spreadsheet for easy editing you can do via</SPAN>
<SPAN style="color:#007F00">'the following example.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> AI() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
<SPAN style="color:#00007F">ReDim</SPAN> AI(ThisWorkbook.Sheets("Sheet2").Range("A65536").End(xlUp).Row)
<SPAN style="color:#00007F">For</SPAN> X = 1 <SPAN style="color:#00007F">To</SPAN> ThisWorkbook.Sheets("Sheet2").Range("A65536").End(xlUp).Row
AI(X) = ThisWorkbook.Sheets("Sheet2").Range("A" & X).Value
<SPAN style="color:#00007F">Next</SPAN> X
<SPAN style="color:#00007F">For</SPAN> X = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(AI)
Me.ComboBox1.AddItem AI(X)
<SPAN style="color:#00007F">Next</SPAN> X
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
<SPAN style="color:#00007F">Dim</SPAN> LoopCounter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> RecordArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> MultipleRecords <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#007F00">'Make this variable true if you want to add an individual record for each category</SPAN>
<SPAN style="color:#007F00">'and amount entry. Make it False if you want multiple amounts to be added out to the</SPAN>
<SPAN style="color:#007F00">'right in extra columns</SPAN>
MultipleRecords = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#007F00">'Clears Previous Records and prepares array for first two values.</SPAN>
<SPAN style="color:#00007F">ReDim</SPAN> RecordArray(1)
<SPAN style="color:#007F00">'Adds Values to Array.</SPAN>
RecordArray(0) = Me.TextBox1
RecordArray(1) = Me.TextBox2
<SPAN style="color:#007F00">'Loads ListBox values into array so they can be easily looped out into multiple columns</SPAN>
<SPAN style="color:#007F00">'across one row.</SPAN>
<SPAN style="color:#00007F">For</SPAN> LoopCounter = 0 <SPAN style="color:#00007F">To</SPAN> Me.ListBox1.ListCount - 1
<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> RecordArray(UBound(RecordArray) + 1)
RecordArray(UBound(RecordArray)) = Mid(Me.ListBox1.List(LoopCounter), 1, InStr(Me.ListBox1.List(LoopCounter), ",") - 1)
<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> RecordArray(UBound(RecordArray) + 1)
RecordArray(UBound(RecordArray)) = Mid(Me.ListBox1.List(LoopCounter), InStr(Me.ListBox1.List(LoopCounter), ",") + 1)
<SPAN style="color:#00007F">Next</SPAN> LoopCounter

<SPAN style="color:#00007F">If</SPAN> MultipleRecords = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">'Adds a record for each item in list box</SPAN>
    <SPAN style="color:#00007F">For</SPAN> LoopCounter = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(RecordArray) <SPAN style="color:#00007F">Step</SPAN> 2
        ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value = RecordArray(0)
        ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 1).Value = RecordArray(1)
        ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 2).Value = RecordArray(LoopCounter)
        ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 3).Value = RecordArray(LoopCounter + 1)
    <SPAN style="color:#00007F">Next</SPAN> LoopCounter
    <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#007F00">'Adds Record to "Sheet1" if you used more than one amount and category it will be added to</SPAN>
    <SPAN style="color:#007F00">'the columns to the right.</SPAN>
    ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value = RecordArray(0)
    <SPAN style="color:#00007F">For</SPAN> LoopCounter = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(RecordArray)
        ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, LoopCounter).Value = RecordArray(LoopCounter)
    <SPAN style="color:#00007F">Next</SPAN> LoopCounter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:#007F00">'This command button action will add the "Amount" and "Category" to your listbox.</SPAN>
Me.ListBox1.AddItem Me.TextBox3.Value & "," & Me.ComboBox1.Value
Me.TextBox3.Value = <SPAN style="color:#00007F">Empty</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>
<hr>

What it does:
CommandButton 1 will add the multiple Amounts to ListBox1. When you have all the amounts in, press Command Button2 to add record. This places the record in Sheet1.
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

That would make sense :biggrin: :LOL:
 

wcthrill

Board Regular
Joined
Nov 28, 2005
Messages
80
Thks Dorang I will wrk on it and let u know of the results. Again thks for taking the time to help out.
 

wcthrill

Board Regular
Joined
Nov 28, 2005
Messages
80
Thks Dorang I will wrk on it and let u know of the results. Again thks for taking the time to help out.
 

wcthrill

Board Regular
Joined
Nov 28, 2005
Messages
80
Oorang said:
Right-click on the UserForm, select "View Code" and

Dorang
when u say userform Im lost. Ive got text boxes, list box, combo box on wrksheet...which is the user form? the actual wrksheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,759
Members
412,482
Latest member
arooshrana2
Top