why Isn't any of the experts helping me?

[joromajr]

-- Hi all,

Badly need your help. My Boss has an eye problem and is requesting a text
box where he can encode and search.

In sheet1 contains account name, Invoice number, invoice dated and Invoice
amount. On Sheet2 account name, Invoice number, Invoice amount, Check number,
check date, and balance amount.

His request, one check box in two parts. on first half is the encoding half,
sections to encode individually account name, invoice number, invoice date,
and invoice amount and a accept button. Second half is the search half,
sections to encode account name and invoice number with search button.

On first half of text box when accept button is click data encoded in the
sections will be sent to sheet1 in proper column and row. On second half
after encoding account and invoice number and search button in clicked the
screen will go to sheet2 to the account name have that invoice number.

Please help need in badly.

joromajr

 

[pallaver]

So rereading your post a couple of times, here's my take on what
you're looking for. Please correct me where I've gone wrong:
-------------
You want a macro with a userform to assist in accounting.
The userform must have two parts:
Part 1: An accept button takes inputted values for account name,
invoice number, invoice date, and invoice amount, and enters them into
Sheet1 (I'm assuming in the order of invoice number, since that makes
the most logical sense).
Part 2: A listbox asks for an account name, and upon entering it
displays all invoice numbers for that account on sheet2.

Is this correct?

-np

 

[joromajr]

-- Thank you, at last, very sorry for not explaining it in proper terms, I
am really new at this.

The answer is "Yes" that is what my boss demands will really appreciate if
you could help me.


joromajr

 

[pallaver]

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

 

[joromajr]

-- you could send your file to (e-mail address removed). 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

 

[joromajr]

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

Thanks

joromajr