Creating a Posting Journal Using VBA and UserForm - No Mechanics are Currently Working

Hello Everyone,

I will begin by admitting I am very new to VBA, but have been at it for months now trying to better myself.

I am currently having some issues with getting my macro to act as a single journal entry (Sheet 2), where is will continuously post on the lines that are blank below the most recent entry. To list a few issues:
1) ComboBoxes dont populate, unless I use a Command Button to populate the ComboBoxes
2) the TextBoxes I have to the right of the UserForm are to pull the description of the account I am accessing - they remain blank indefinetely
3) The posting itself does not process
4) When I do populate my ComboBoxes (by the command button stated), it does not differentiate the accounts from what it originally placed in the 2nd ComboBox (Posting Account) and will actually list all accounts; Assets, Liabilities, Revenues, etc.

I know my code is VERY sloppy, but any help towards a direction that I should be heading towards would be the greatest help ever! I have attached the excel workbook to this thread.


WesRuss

Hi WesRuss,

Without changing too much of your code, I have made a few changes to get you started.
The main change is that Combo Box's 1 and 2 will initialize when the userform is activated using

Please Login or Register  to view this content.

ComboBox 3 will also be activated once a selection is made in ComboBox 2.

User Form Example.xlsm

Let me know how you go.

Hello WessRuss

I've made a couple of changes to your code to make it work.
I see you have 6 AddItem lists for ComboBox3. You'll have to change that to one list & change some of the codes. Either that or add more combo boxes or something.
I haven't gone mad with changes, just enough so you can see how it works

It's attached, see how it goes
Cheers
Phil

Thank you both! The macro works perfectly now!

Hi WesRuss,

You may want to think about doing something like this that posts both the credit and debit at the same time.
Also I have removed the excess text boxes as well and shortened the code.

Copy of User Form Example.xlsm

Glad you're up & running, nothing worse then bashing your head against the wall lol

I do like the double posting code as well - the only reason I was trying to stay away from that was for the cases where it wasnt just a two transactional posting, (Ex. labor costs)

I had modified a good amount of the overall workbook - mainly display - and have run into a situation that I am just 100% unsure about hwo to proceed.

I now need all transactions that I post each day transferred from the "General Journal" sheet to its accomodating GL. The format for teh GLs are ###.### - same as what you saw from the original lists.

However, I do not wish to make a simple if then statement custom for each GL, but a universal code that matches column B and C with the GL worksheet name.

Any idea what I should do with this? Attached is my most recent Excel.UserForm.xlsm

Agreed...something like this should suffice. I have tried to use code that you are familiar with to make it easier for you.
I have used a Do Until loop to loop through each line in the General Journal worksheet and copy the data to the relevant tab.

Please Login or Register  to view this content.

Thank you both again. I had tried both you scenarios and both have sent me back some errors.

SpitFireBlue:

Your code gives no errors, but does not copy any data to other sheets - not sure if I put the code in the wrong place

Philb1:

Your code is very complex - which is awesome - and I understand it all. The only issue is that I finished the Case Is sections, however the "Set TargetWs = ThisWorkbook.Sheets(ShtNum)" is coming back an error and always defaults to the error message, but will post the data in the "General Journal" correctly, but not copy.

I have attached both options to this thread. SpitFireBlue - your code is in UserForm2, Philb1 - yours is in UserForm1.

Try this...

Copy of UserForm 2.xlsm