entry accrued

[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

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

[sna]

Thanks but how to create serial like A201222-001
A201222-002
A201222-003
...
In column A with code
Thanks

[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

[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

[sna]

How about to create serial number like A201222-001
A201222-002
A201222-003
...


Thanks

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

[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

[puriwutpokin]

Try writing code yourself first. What kind And then make a sample of that file I want it.

[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,

[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

[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

[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