Page 1 of 1
entry accrued
Posted: Tue Dec 22, 2020 9:32 pm
by sna
Hi Dear!
I create a data entry form for listing accrued expenses in excel but I am stuck how to create serial number after data entry in column B.serial number format letter A and Year 2 digits month 2 digits day 2 digits and "-" and serial 00X(001,002....)
I also don't know why my edit button not working
I attached a template
Thanks
Re: entry accrued
Posted: Tue Dec 22, 2020 10:51 pm
by puriwutpokin
Try edit
Code: Select all
RecordRow = Application.Match(CStr(TextBox7.Value), Range("Table1[Serial No]"), 0)
Code: Select all
'Other...
TextBox1.Value = Cells(5, 1).Offset(RecordRow, 1).Value
TextBox2.Value = Cells(5, 1).Offset(RecordRow, 2).Value
TextBox3.Value = Cells(5, 1).Offset(RecordRow, 3).Value
TextBox4.Value = Cells(5, 1).Offset(RecordRow, 4).Value
TextBox5.Value = Cells(5, 1).Offset(RecordRow, 5).Value
TextBox6.Value = Cells(5, 1).Offset(RecordRow, 6).Value
'Other...
Re: entry accrued
Posted: Wed Dec 23, 2020 6:46 am
by sna
Thanks but how to create serial like A201222-001
A201222-002
A201222-003
...
In column A with code
Thanks
Re: entry accrued
Posted: Wed Dec 23, 2020 8:36 am
by sna
I try changing it works but show the data we search.I want to click new it will amend the previous data.could you give more code to proceed?
Thanks
Re: entry accrued
Posted: Wed Dec 23, 2020 10:46 am
by puriwutpokin
Modify old code and use button New
Code: Select all
Private Sub CommandButton2_Click()
Dim RecordRow As Long
Dim RecordRange As Range
On Error Resume Next
RecordRow = Application.Match(CStr(TextBox7.Value), Range("Table1[Serial No]"), 0)
Set RecordRange = Range("Table1").Cells(5, 1).Offset(RecordRow - 1, 0)
If Err.Number <> 0 Then
ErrorLabel.Visible = True
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0
ErrorLabel.Visible = False
Cells(5, 1).Offset(RecordRow, 1).Value = TextBox1.Value
Cells(5, 1).Offset(RecordRow, 2).Value = TextBox2.Value
Cells(5, 1).Offset(RecordRow, 3).Value = TextBox3.Value
Cells(5, 1).Offset(RecordRow, 4).Value = TextBox4.Value
Cells(5, 1).Offset(RecordRow, 5).Value = TextBox5.Value
Cells(5, 1).Offset(RecordRow, 6).Value = TextBox6.Value
End Sub
Re: entry accrued
Posted: Wed Dec 23, 2020 12:37 pm
by sna
How about to create serial number like A201222-001
A201222-002
A201222-003
...
Thanks
Re: entry accrued
Posted: Wed Dec 23, 2020 3:10 pm
by puriwutpokin
It Sample Code
Code: Select all
'Other code...
Rng.Offset(, 5).Value = TextBox6.Value
Rng.Offset(, -1).Value = Format(TextBox6.Value, "Ayymmdd") & _
Right(Format(WorksheetFunction.CountA(Range("Table1[Accrued Account]")), "-0000"), 5)
'Other code...
Re: entry accrued
Posted: Wed Dec 23, 2020 3:31 pm
by sna
Really appreciate.it works
once more how can we transfer by row as per below:
Expense account to journal sheet in column Debit Acc
Accrued Account to journal sheet in column Credit Account
Amount to journal sheet in column Amount
Class to journal sheet in column Location
Best Regards
Re: entry accrued
Posted: Wed Dec 23, 2020 4:21 pm
by puriwutpokin
Try writing code yourself first. What kind And then make a sample of that file I want it.
Re: entry accrued
Posted: Wed Dec 23, 2020 5:20 pm
by sna
Hi Dear
I need to copy Accrued account, Expense Account and Amount To Journal Sheet.
I draw a shape toward the result I need.
I want to open workbook hide sheetJournal .but when I click JV button on userform it show sheet journal and I can click back to sheet data but hide sheet journal as normal.
Hope you may help
Best Regards,
Re: entry accrued
Posted: Wed Dec 23, 2020 7:57 pm
by puriwutpokin
Sample code
Code: Select all
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr As Long, i As Long, j As Long
j = 11
Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Journal")
ws2.Visible = True
lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
For i = 6 To lr
If ws1.Cells(i, 1).Value <> "" Then
ws2.Cells(j, 1).Value = ws1.Cells(i, 1).Offset(, 2).Value
ws2.Cells(j, 2).Value = ws1.Cells(i, 1).Offset(, 1).Value
ws2.Cells(j, 4).Value = ws1.Cells(i, 1).Offset(, 3).Value
j = j + 1
End If
Next
ws2.Visible = False
Re: entry accrued
Posted: Thu Dec 24, 2020 8:22 am
by sna
Hi when I click JV button it hides journal sheet.
I mean when open workbook journal sheet hide but when click JV button the journal sheet show with value from data sheet.
Hope you got it
note the result in journal would be like the shape I draw on the right
Thanks
Re: entry accrued
Posted: Sun Dec 27, 2020 8:15 am
by sna
Hi Dear,
I need that way as I open the template.Excel Auto hide Journal Sheet and Show only Data Sheet but when I click Load form then click JV it will copy some data from Data Sheet to Journal Sheet and hide Data Sheet
Thanks