Posts by prragasa

Hello, it's been awhile.

I've already made version 7 of the General Ledger file.

Same as before, the edits in 'Unit Code' to 'Item Code' columns in 'Budget' worksheet should be copied to 'Unit Code' to 'Sub Code' columns in 'Chart of Accounts' worksheet.

In addition, the edits in 'Period No.' to:

a) 'Amount Paid' columns in 'Purchases' worksheet

b) 'Amount' columns in 'Disbursements' worksheet

c) 'Amount Reimbursed' columns in 'Petty Cash' worksheet

d) 'Cr. Amount' columns in 'Cashbook' worksheet

should be copied to 'Period No.' to 'Cr. Amount' columns in 'General Journal' worksheet.

I've tried using the code as above and twerk it, but I can't seem to figure how to make it work.

I created a Splash Screen based on this website: https://wellsr.com/vba/2016/ex…een-for-your-spreadsheet/

I coded it using a Windows laptop, since Userforms are not found in Excel for Mac 2019. It works fine there, but when I open it on my MacBook, I get an error about 64-bit etc. It points to this specific code I got from the website above:

Public Declare Function GetWindowLong _
                       Lib "user32" Alias "GetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong _
                       Lib "user32" Alias "SetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long, _
                       ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar _
                       Lib "user32" ( _
                       ByVal hWnd As Long) As Long
Public Declare Function FindWindowA _
                       Lib "user32" (ByVal lpClassName As String, _
                       ByVal lpWindowName As String) As Long

Is there any way to resolve this?

Re: Sorting

I want to sort the table based on the "Budget Ref." column in "Budget" table and "Account Code" column in "COA" in an ascending order.

Quote from Carim

Regarding one Command Button to trigger Edit and Save :

How would you plan to correctly indicate which row from the Source sheet needs to be updating which row from the Destination sheet ?

I guess that would be a problem, since in the latest version that I edited (see V3 below) I intend to identify each row with Budget Ref. = Account Code. But the Budget Ref. changes right away once I edit it.

Double click is possible, I guess, but maybe not limit it to Budget Item, I guess? Allow Edit and Save in Unit Code, Category Title and Item Code as well?

Quote from Carim

Hello,

You are tackling too many topics at the same time ...

It is much safer to go step by step ...

Regarding the Edit / Save question ... attached is Version 2

If you are modifying a Budget Item in Column G ... you only need to double-click on the new choice ... and it will replace the old one in the Chart of Accounts worksheet ...

Hope this will help

Display More

Thanks again!

However, all 4 columns and not just 'Budget Item' should be updated.

I'm also thinking whether a command button is more apt to trigger Edit and Save than just double clicking. What do you think?

Quote from Carim

Hello,

What you are requesting is indeed feasible ... as long as there is a Unique Identifier

Why don't you create an example in your test file before attaching it ...

Here's the file with sample data entries in both tables.

I've started adding another worksheets, but they are not yet done.

Also, I've tried sorting both tables automatically based on the "Budget Ref./Account Code" columns once a new row of data is completely entered.

This is a sample VBA Code I found on the internet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("J:J")) Is Nothing Then
Range("J4").Sort Key1:=Range("J5"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

However it doesn't seem to work.

Just a follow up: is it possible to reflect any changes in the 'Budget' table data row reflect in the 'COA' table data row?

So that when I change, for example, an existing 'Budget Item' data in 'Budget', the change is reflected in the corresponding 'Sub Account' data in 'COA'.

Like an "Edit and Save" button, maybe?

Quote from Carim

Re,

Attached is your Test file as a starting point ...

Your input in Column H ( Budget Item) triggers the copy process ...

Hope this will help

Display More

This helps a lot! Thank you so much!

Quote from Carim

Not sure to fully understand your process ... and the need for a Command Button for each newly added row ...

A couple of remarks :

1. If your Table 'COA' is only a copy of the 'Budget Table' ... an equal sign between the two tables would do the job

2. If your Table 'COA' is NOT a replicate of the 'Budget Table' ... then ...as you input the Budget Item in Column H, an event could handle the copy process ...

Display More

I think no. 2 applies in my case. COA is not a replicate of Budget table, since Budget table only creates Expense accounts.

In COA table, I need to create Asset, Liability, and Equity accounts, in addition to the Expense accounts I will create by copying those data already in the Budget table.

I'm not really sure how to do the exact copying process

So that every time I create a new row of data in the Budget table, a command button appears which allows me to copy it directly to the COA table.

Quote from Carim

Hello,

Currently both your macros both expand the size of your tables ...

Can you explain how you are planning to use your General Ledger ...

And specifically what data needs to be copied from which Source to which Destination ...

Display More

Hi Carim! I'm creating a Film Accounting file for my production company.

I want to copy from Budget table to COA table he following:

Category Title > Account Title

Budget Code > Budget Code

Item Code > Sub Code and

Budget Item > Sub Account

A new row may be automatically added in table 1 if the above row is populated (instead of clicking Add Budget Item) but I also don't know the appropriate code.

Hello!

I have a dynamic table in sheet 1 (Budget) where specific cells must be copied to a new row in another dynamic table in sheet 2 (COA), so that:

Entry in Category Title in table 1 is copied to Account Title in table 2,

Budget Code to Budget Code,

Item Code to Sub Code, and

Budget Item to Sub Account.

A command button (Create Expense A/C) should appear in each new row added in table 1 via the existing Add Budget Item command table, which if clicked will trigger adding another row in table 2 and the copying process.

I'm fairly new to VBA using Excel for Mac 2019 and so I am not sure how to make this happen. I'm attaching the file for reference.

Hoping someone could help me on this, thanks!