POS on excel

Question

Have a POS(point of sale) workbook that works. Want to ad buttons (New Sale, Add to customer account and Cash Sale), Then on customer account it must show products taken and date with price.

Worksheet 1 POS with Barcode scanner Customer name, Product ID, Product Name, Price, Total,

Worksheet 2 Customer Name, Date, Products taken, Amount

Answer 1

Hi Martiens

I hope the video in the link could help you and give a solution to your problem

https://youtu.be/cNpFB8M1-dI?si=5dIVeOxKvhuagbJP

Do let me know if you need more help

Kind Regards

Jeovany

Answer 2

Can you create a ready to use Excel POS for me to download. POS Screen with buttons(New Sale, Cash Sale & ad to Members account). Members Account worksheet with date, products taken and Price.

Al working with Barcode scanner (Members ID & Product scan)

Answer 3

Can you develop a ready to use download file

Answer 4

Use Excel Form controls or ActiveX controls on the POS worksheet, and then use VBA to write the POS line items to the customer account sheet when a button is clicked.

1. Insert buttons on Worksheet 1 (POS)

• On the ribbon, go to the Developer tab.
• Choose either:
  • Insert → Form Controls → Button, or
  • Insert → ActiveX Controls → Command Button.
• Draw three buttons on Worksheet 1 and label them:
  • New Sale
  • Add to customer account
  • Cash Sale

Form controls are simpler and can be directly assigned to macros. ActiveX command buttons give more flexibility (appearance, events, etc.) but require VBA event procedures.

2. Assign macros (Form controls)

• Right‑click each Form control button → Assign Macro….
• Create three macros in a standard module, for example:
  • Sub NewSale()
  • Sub AddToCustomerAccount()
  • Sub CashSale()

3. Example logic for AddToCustomerAccount Assume on Worksheet 1 (POS):

• Customer name in a fixed cell, e.g. B2.
• Date in a fixed cell, e.g. B3.
• Line items in a table with columns: Product ID, Product Name, Price, Total. Assume Worksheet 2 (Customer Account) has columns:
• A: Customer Name
• B: Date
• C: Products taken
• D: Amount

The macro should:

1. Read the customer name and date from Worksheet 1.
2. Loop through the POS line items and build a text string of products taken (e.g. “Prod1 x2; Prod2 x1”).
3. Sum the line totals for the amount.
4. Find the next empty row on Worksheet 2.
5. Write Customer Name, Date, Products taken, Amount into that row.
6. Using ActiveX controls (optional) If using ActiveX Command button instead of Form controls:

• Insert the command button from Developer → Insert → ActiveX Controls → Command Button.
• Right‑click the button → View Code.
• In the generated CommandButton_Click event, place the same logic that would be in the AddToCustomerAccount macro.

ActiveX controls allow more customization and can respond to events (click, change, etc.), which is useful if the POS form needs more complex behavior.

5. New Sale and Cash Sale

• New Sale macro: clear the POS entry area on Worksheet 1 (customer name, date if needed, and all product lines) to start a fresh transaction.
• Cash Sale macro: similar to AddToCustomerAccount, but either:
  • writes to a separate “Cash Sales” sheet, or
  • writes to Worksheet 2 with a specific marker (e.g. customer name “CASH”) depending on the design.

The key pieces are:

• Use Form or ActiveX command buttons on the worksheet.
• Use VBA macros or event procedures to:
  • Read the POS data from Worksheet 1.
  • Aggregate products and totals.
  • Append a new record to Worksheet 2 with Customer Name, Date, Products taken, and Amount.

---

References:

• Overview of forms, Form controls, and ActiveX controls on a worksheet
• Online and offline point of sale (POS) operations
• Basic tasks using a screen reader with Excel