-- Hope that you could send the excel file so I could learn from It...

Thanks

joromajr


"joromajr" wrote:


-- you could send your file to . Will wait hope that can
continue to monitor my progress. I really need your help and my gratitude as
we continue with this case.


joromajr


"pallaver" wrote:

Hi Joromajr,

I will send you the excel file tonight hopefully using the email
address listed, but here's what I've done cut and pasted so you can
mimick it:

First, in sheet1 A1 to A4 put the following:
A1 Invoice Number
A2 Invoice Date
A3 Invoice Amount
A4 Account Name

In sheet2 A1 to A4 put the following:
A1 Invoice Number
A2 Invoice Date
A3 Invoice Amount
A4 Account Name

Now get into your visual basic editor, and in Module1 copy paste the
following between the dashed lines:

-------------------

Option Explicit

Public TotalNumberInvoices As Integer
Public TempInvoiceNumber As String
Public i As Integer
Public j As Integer
Public EntryLoop As Integer
Public CompanyNames(0 To 10000) As String
Public ArrayNumber As Integer



Sub Accounting()

EntryLoop = 0

i = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
If i 1 Then
Sheets("Sheet2").Rows("2:" & (i)).ClearContents
End If

Sheets("Sheet1").Select

Do Until EntryLoop = 1
userform1.Show
Loop

Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").Select

End Sub
-------------------

Now create a userform. In the userform you want 4 text boxes (for
inputting each of the 4 variables), an enter in inputted information
command button, and a combobox (for your search function, part two).
You'll have to make the userform yourself, but this is not that
difficult.

I'll let you figure out what name to give each text box and the
combobox, noting that a textbox variable name ends with TB, and the
combobox variable name ends with CB. If you look at the code, you
should be able to figure it out. Double click on the userform and in
the userform code box put in the following:

-----------------

Option Explicit


Private Sub CompanyNamesCB_Change()

j = 2

For i = 2 To (TotalNumberInvoices + 2)

If Sheets("Sheet1").Cells(i, 4).Value = CompanyNamesCB.Value Then
Sheets("Sheet2").Cells(j, 4).Value = Sheets("Sheet1").Cells(i,
3).Value
Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i,
4).Value
Sheets("Sheet2").Cells(j, 2).Value = Sheets("Sheet1").Cells(i,
1).Value
Sheets("Sheet2").Cells(j, 3).Value = Sheets("Sheet1").Cells(i,
2).Value
j = j + 1
End If

Next i

EntryLoop = 1
userform1.Hide

End Sub

Private Sub EnterInvoice_Click()

If InvoiceNumberTB.Value = "" Or DateInvoiceTB.Value = "" Or
AmountInvoiceTB.Value = "" Or AccountNameCB.Value = "" Then Exit Sub

For i = 2 To (TotalNumberInvoices + 2)

If Val(InvoiceNumberTB.Value) = Sheets("Sheet1").Cells(i, 1).Value
Then
MsgBox "Sorry, this Invoice Number is already taken."
InvoiceNumberTB.Value = ""
Exit For
End If

If Val(InvoiceNumberTB.Value) < Sheets("Sheet1").Cells(i, 1).Value
Then
Rows((i) & ":" & (i)).Insert Shift:=xlDown
Sheets("Sheet1").Cells(i, 1).Value = InvoiceNumberTB.Value
Sheets("Sheet1").Cells(i, 2).Value = DateInvoiceTB.Value
Sheets("Sheet1").Cells(i, 3).Value = AmountInvoiceTB.Value
Sheets("Sheet1").Cells(i, 4).Value = AccountNameCB.Value
Exit For
End If

If i = TotalNumberInvoices + 2 Then
Sheets("Sheet1").Cells(i, 1).Value = InvoiceNumberTB.Value
Sheets("Sheet1").Cells(i, 2).Value = DateInvoiceTB.Value
Sheets("Sheet1").Cells(i, 3).Value = AmountInvoiceTB.Value
Sheets("Sheet1").Cells(i, 4).Value = AccountNameCB.Value
Exit For
End If

Next i


InvoiceNumberTB.Value = ""
DateInvoiceTB.Value = ""
AmountInvoiceTB.Value = ""
AccountNameCB.Value = ""

End Sub



Private Sub Frame2_Click()

End Sub

Private Sub UserForm_Activate()

CompanyNamesCB.Clear


TotalNumberInvoices = Sheets("Sheet1").Range("A" &
Rows.Count).End(xlUp).Row - 1

ArrayNumber = 0

For i = 2 To (TotalNumberInvoices + 2)

If i = 2 Then
CompanyNamesCB.AddItem (Sheets("Sheet1").Cells(i, 4).Value)
AccountNameCB.AddItem (Sheets("Sheet1").Cells(i, 4).Value)
CompanyNames(ArrayNumber) = Sheets("Sheet1").Cells(i, 4).Value
ArrayNumber = ArrayNumber + 1
Else

For j = 0 To ArrayNumber
If Sheets("Sheet1").Cells(i, 4).Value = CompanyNames(j) Then
Exit For
If j = ArrayNumber Then
CompanyNamesCB.AddItem (Sheets("Sheet1").Cells(i,
4).Value)
AccountNameCB.AddItem (Sheets("Sheet1").Cells(i, 4).Value)
CompanyNames(ArrayNumber) = Sheets("Sheet1").Cells(i,
4).Value
ArrayNumber = ArrayNumber + 1
End If
Next j
End If

Next i

End Sub

----------------------

And there you go.

The code is by no means dummy proof - i.e. makes sure that dates are
correct dates (i.e. you can't enter July 45th), or that the number
contains no letters or two decimal points, this just makes it a little
more complicated, but if the user carefully enters in the info, it
should work.

Try it out, post back questions or where things are going wrong.

Since, np