Payment UserForm through Visual Basic Editor in Excel

switch language to bahasa

The topic in this page is the continuation from the previous page. So now Mr. Adi is going to create a Payment UserForm through Visual Basic Editor. The difference between form in part-3 with the UserForm in this topic is : the form in part-3 is created on the worksheet – the UserForm in this topic will be created via Visual Basic Editor (VBA).

This UserForm will be used to input invoice number payment, either from the accounts receivable (Customer) or to the accounts payable (Supplier).

Based on Mr. Adi experience, the workflow of this form will be a little complicated because there are a few differences on how the payment is made. Basically, there are three model of the payment :

  • Model-A, don’t care for the invoice/s. The focus is only to the last balance.
    Example : The last balance is 1,123,456,789. Pay 300,000. So the balance now
    is 823,456,789.
  • Model-B, care for the invoice/s from a certain month. Maybe all the invoices of month X are paid, maybe only some invoices of month X are paid.
  • Model-C, care for the invoice/s but don’t care from what month is the invoice/s.

Each model will be explained more when the UserForm has been made on the next topic.

Prepare the worksheet

Similar with part-3, the first thing Mr. Adi do is creating a new worksheet with a name “PAY”. So here, although the form will be a “standalone” form (it’s not on the worksheet) but the sheet is still needed as a helper to see the invoices which are going to be inputted to the main data.

So, in sheet “PAY”, Mr. Adi fill cell C4 with =today() for the date when he input the payment. Fill cell B6 with “Invoice Number”, cell C6 with “VALUE”, cell E6 with “NAME” and cell F6 with “AMOUNT”. It looks like the sample image below.

Naming the worksheet.
Please ignore the foreign name in the image.

And then he create two Pivot Tables in sheet TABLE by copying any of the existing Pivot Table then paste it on sheet TABLE.

For the first Pivot Table, paste it on sheet TABLE cell H1, and name it “ptInvoice”.

Arrange the fields as follow :
Have INV field in Row Labels area.
Have NAME field in Row Labels area under the INV field.
Have SUM field in Values area, format it as needed.

The result looks similar with the image below.

For the second Pivot Table, paste it on sheet TABLE cell L1, and name it “ptPayment”.

Arrange the fields as follow :
Have NAME field in Report Filter area.
Have Y/M field in Row Labels area.
Have INV field in Row Labels area, under Y/M field.
Have SUM field in Values area, format it as needed.

The result looks similar with the image below.

Designing a Payment UserForm through Visual Basic Editor

In “Developer” menu tab, click “Visual Basic” button. Mine is located on the most left of the menu, as seen in the image below.

Visual Basic Editor menu to create Payment UserForm.

Appear a Microsoft Visual Basic for Applications (VBA) window, as seen in the image below.

The Visual Basic Project window.

On the left pane, there are two small windows, Project – VBAProject window and Properties window.

Right-click anywhere inside the Project – VBAProjects window, click “Insert” then click “UserForm”. As seen in the image below.

Step to create the Payment UserForm in Excel

Appear another two windows. “Toolbox” window and “UserForm” (with a UserForm1 caption) window. As seen in the image below.

blank excel userform din visual basic editor.

In “Properties” window, change the (Name) from UserForm1 into frmPayment, and change the Caption from UserForm1 into PAYMENT FORM. As seen in the image below.

Mr. Adi resize the UserForm by dragging the bottom-right corner of the UserForm. As seen in the animation below.

Resize the canvas of the UserForm

There are 5 kind of controls in Toolbox which are needed. Label, TextBox, ListBox, Frame and CommandButton. As seen in the image below.

The controls needed

Just like in part-4, to create the control, just click the control in the Toolbox window, then click anywhere insidde the UserForm’s “canvas”. As seen in the animaton below.

Create the control
gambar-10

Create each control as many as the following :

CONTROL AMOUNT
Label 1
TextBox 1
ListBox 3
Frame 2
CommandButton 7
All the controls needed for the Payment UserForm through Visual Basic Editor in Excel

If one of the control in the “canvas” is clicked, the Properties window will show the properties of the clicked control. As seen in the animation below.

Properties of each control

Next, Mr. Adi change the (Name) and the Caption of each control in the “Properties” window. Just click the control, then change the (Name) and the Caption in the “Properties” window as follow :

Control’s default (Name) (Name) change to Caption
Label1 lblSum SUM
TextBox1 SearchBox  
ListBox1 ListName  
ListBox2 ListYM  
ListBox3 ListInv  
Frame1 Choose CU / SU
Frame2 Collect Invoice
CommandButton1 CU CU
CommandButton2 SU SU
CommandButton3 RESET RESET
CommandButton4 ColAllInv All Invoice
CommandButton5 ColSelectedInv Selected Invoice
CommandButton6 ColDisregardInv Type the payment value
CommandButton7 InputToData Input To Data

Finally, Mr. Adi arrange the size and the position of each control according to his like. How is the size and where is the position depends on the taste of the person who designing it. Anyway in this case, Mr. Adi arrange the controls like the image below :

Designing Payment UserForm through Visual Basic Editor in Excel has finished.
Inside the left-frame
CONTROL USED FOR
CU Payment from a customer
SU Payment to a supplier
RESET Clear sheet PAY
SearchBox type the name to be searched
ListName Name list found based on the typed word
Type the payment value Payment which disregard invoice number
Input To Data Put collected invoices to the main data
Inside the right-frame
CONTROL USED FOR
SUM SUM value of the invoices
ListYM The Month and Year of the invoice
ListInv Invoice List based on the selected element
All Invoices Collect invoice’s based on the selected element
Selected Invoice Collect the invoice based on the selected invoice

That’s it. The designing stage to make the Payment UserForm through Visual Basic Editor in Excel has finished. In the next topic, Mr. Adi will make the macro code for the controls in this UserForm.

If there is something which is not clear regarding to this topic, please don’t hesitate to ask in the Comment box. I’ll try my best to answer.

205 replies on “Payment UserForm through Visual Basic Editor in Excel”

I like the helpful info you provide in your articles. I’ll bookmark your blog and check again here regularly. I’m quite certain I will learn a lot of new stuff right here! Best of luck for the next!

Aw, this was a really good post. Finding the time and actual effort to make a really good article… but what can I say… I put things off a lot and never seem to get anything done.

บาคาร่าออนไลน์จะต้องยกให้เป็นที่สุดของเกมไพ่ออนไลน์เลยนะครับ เพราะเหตุว่าเข้าใจง่ายไม่ซับซ้อนที่

I feel that is among the so much vital information for me. And i’m happy studying your article. However want to commentary on few common things, The site taste is great, the articles is in point of fact nice : D. Good process, cheers

What’s Going down i am new to this, I stumbled upon this I have discovered It absolutely helpful and it has helped me out loads. I’m hoping to contribute & aid different customers like its aided me. Good job.

I and my guys have been going through the excellent items from your web page and suddenly got a horrible suspicion I had not expressed respect to the web site owner for those strategies. Most of the ladies came for that reason happy to see all of them and have in effect in reality been making the most of those things. We appreciate you truly being really considerate as well as for settling on this sort of incredible guides millions of individuals are really desirous to be aware of. Our sincere apologies for not expressing appreciation to you sooner.

The next time I learn a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my option to read, but I really thought youd have something attention-grabbing to say. All I hear is a bunch of whining about one thing that you possibly can fix in the event you werent too busy looking for attention.

Aw, this was a really nice post. In thought I want to put in writing like this additionally – taking time and precise effort to make a very good article… however what can I say… I procrastinate alot and not at all seem to get something done.

Hey would you mind sharing which blog platform you’re working with? I’m looking to start my own blog soon but I’m having a difficult time selecting between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I’m looking for something unique. P.S My apologies for getting off-topic but I had to ask!

I have been surfing on-line more than three hours these days, but I by no means found any interesting article like yours. It’s lovely value enough for me. Personally, if all website owners and bloggers made just right content material as you probably did, the web shall be much more helpful than ever before.

I like the valuable information you supply on your articles.I will bookmark your blog and check again here frequently.I am somewhat sure I will be told many new stuff right here!Best of luck for the next!

Thank you for any other excellent article. The place else may just anybody get that type of information in such a perfect manner of writing? I’ve a presentation subsequent week, and I’m at the search for such information.

Good day! This post couldn’t be written anybetter! Reading this post reminds me of my good old room mate!He always kept chatting about this. I will forward this article to him.Pretty sure he will have a good read. Many thanks for sharing!

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

A fascinating discussion is worth comment. There’s no doubt that that you ought to publish more on this subject, it may not be a taboo subject but generally people don’t talk about such issues. To the next! Kind regards!!

I do believe all the ideas you have presented in your post. They are very convincing and will definitely work. Nonetheless, the posts are too quick for newbies. May just you please prolong them a bit from next time? Thanks for the post.

สำหรับ การเล่นสล็อต นั้นมีพัฒนาการมาอย่างยาวนาน ในสมัยก่อนจะมีลักษณะเป็นตู้สล็อตแมชชีนที่ วิธีเล่นสล็อต จะใช้คันโยกเป็นตัวเสี่ยงรางวัล และรอลุ้นว่าสัญลักษณ์ในช่องจะตรงกันหรือไม่เพื่อรับรางวัลเป็นต้น

I live in London happy golden ox of happiness slots free play Chancellor returned and reintegrated in the 24-20 Week 11 loss to the Chiefs, and that was the last time Seattle lost

Aw, this was an extremely good post. Taking the time and actual effort to generate a really good articleÖ but what can I sayÖ I procrastinate a whole lot and don’t manage to get nearly anything done.

I will right away snatch your rss as I can’t findyour e-mail subscription hyperlink or e-newsletter service.Do you have any? Please allow me understand so that I may just subscribe.Thanks.

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

Leave a Reply

Your email address will not be published. Required fields are marked *