Form Design

wcthrill · Dec 30, 2005

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

Oorang · Dec 30, 2005

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 · Dec 30, 2005

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 · Dec 30, 2005

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 · Jan 1, 2006

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>
Private Sub UserForm_Initialize()
'If you would like to put your categories in a spreadsheet for easy editing you can do via
'the following example.
Dim AI() As String
Dim X As Byte
ReDim AI(ThisWorkbook.Sheets("Sheet2").Range("A65536").End(xlUp).Row)
For X = 1 To ThisWorkbook.Sheets("Sheet2").Range("A65536").End(xlUp).Row
AI(X) = ThisWorkbook.Sheets("Sheet2").Range("A" & X).Value
Next X
For X = 1 To UBound(AI)
Me.ComboBox1.AddItem AI(X)
Next X
End Sub

Private Sub CommandButton2_Click()
Dim LoopCounter As Byte
Dim RecordArray() As String
Dim MultipleRecords As Boolean
'Make this variable true if you want to add an individual record for each category
'and amount entry. Make it False if you want multiple amounts to be added out to the
'right in extra columns
MultipleRecords = False

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

If MultipleRecords = True Then
 'Adds a record for each item in list box
 For LoopCounter = 2 To UBound(RecordArray) Step 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)
 Next LoopCounter
 Else
 'Adds Record to "Sheet1" if you used more than one amount and category it will be added to
 'the columns to the right.
 ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0).Value = RecordArray(0)
 For LoopCounter = 1 To UBound(RecordArray)
 ThisWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, LoopCounter).Value = RecordArray(LoopCounter)
 Next LoopCounter
 End If
End Sub

Private Sub CommandButton1_Click()
'This command button action will add the "Amount" and "Category" to your listbox.
Me.ListBox1.AddItem Me.TextBox3.Value & "," & Me.ComboBox1.Value
Me.TextBox3.Value = Empty
End Sub


<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.

HalfAce · Jan 2, 2006

Oorang said:
Not sure what you mean by PT.

My bet is that "PT" = Pivot Table...

Oorang · Jan 2, 2006

That would make sense

wcthrill · Jan 3, 2006

Thks Dorang I will wrk on it and let u know of the results. Again thks for taking the time to help out.

wcthrill · Jan 3, 2006

Thks Dorang I will wrk on it and let u know of the results. Again thks for taking the time to help out.

wcthrill · Jan 3, 2006

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?

Form Design

wcthrill

Board Regular

Excel Facts

Oorang

Well-known Member

Razzle0147

New Member

wcthrill

Board Regular

Oorang

Well-known Member

HalfAce

MrExcel MVP

Oorang

Well-known Member

wcthrill

Board Regular

wcthrill

Board Regular

wcthrill

Board Regular

Similar threads

Forum statistics

Share this page

Form Design

Board Regular

Excel Facts

Well-known Member

New Member

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock