|
why Isn't any of the experts helping me?
-- 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 |
why Isn't any of the experts helping me?
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 On 731, ȫe10:27, joromajr wrote: -- 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 |
why Isn't any of the experts helping me?
-- 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" wrote: 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 On 7月31日, 午前10:27, joromajr wrote: -- 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 |
why Isn't any of the experts helping me?
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 |
why Isn't any of the experts helping me?
|
why Isn't any of the experts helping me?
|
| All times are GMT +1. The time now is 01:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com