User Form Combo Box > Multiple Seletion instead of 1?

[Stephan]

Hello
FILE XLSM 0.8MB attached
http://www.hkrebs63.karoo.net/xl/SALESxlsm.zip

FILE MACRO XLS 1MB:
http://www.hkrebs63.karoo.net/xl/SALES.zip

TAB: NEW SALE > USERFORM: SALE

NEW SALE spreadsheet emulates EPOS system, think this is quite neat but needs further edit to make its purpose more functional, hence question below, and ONCE there is a solution to this, the sorting of products into categories would narrow selection to more relevant products but this should be relatively straightforward possibly define name list.

QUESTION:
SALE USER FORM/MACRO edit for MULTIPLE TRANSACTION instead of SINGLE ITEM? (To edit VB right/click tab NEW SALE > View Code. Forms > USERFORM2 right/click View Code. This is the Macro for the BUY button at the end/btm right of this SALE UserForm).

XLSM FILE: http://www.hkrebs63.karoo.net/xl/SALESxlsm.zip
(also attached to this Forum post)http://www.hkrebs63.karoo.net/xl/SALESxlsm.zip
XLS FILE: http://www.hkrebs63.karoo.net/xl/SALES.zip
TAB: NEW SALE
COMBO BUTTON: SALE
USERFORM: NEW SALE

To describe in words:
1st seletion = PRODUCT/ITEM NUMBER (BarCode equivalent) then following UserForm Combo Boxes auto populate depending on code such as description. Realise this seems confusing but is meant to be emulate EPOS scan item to display description, but can be edited so that all different products are listed in 1st combobox instead.

SALE userform selects from STOCKFILE available codes, but only 1 PRODUCT TYPE at a time can be enter via USER FORM SALE > SALES.
1st seletion = PRODUCT/ITEM NUMBER (BarCode equivalent) then following UserForm Combo Boxes auto populate depending on code such as description. Realise this seems confusing but is meant to be emulate EPOS scan item to display description, but can be edited so that all different products are listed in 1st combobox instead.

SALE userform selects from STOCKFILE available codes, but only 1 PRODUCT TYPE at a time can be enter via USER FORM SALE > SALES.

Cheers Stephan

 

[AlanSidman]

Many members here are reluctant to open 3rd party sites that are unknown to them for security and safety reasons. Suggest you upload your file to this forum. Click on Upload a File in the response thread.

 

[Stephan]

Many members here are reluctant to open 3rd party sites that are unknown to them for security and safety reasons. Suggest you upload your file to this forum. Click on Upload a File in the response thread.

Hi Good point, attached XLSM zip version and also hyperlink:
http://www.hkrebs63.karoo.net/xl/SALESxlsm.zip

Also debug VB, currently this spreadsheet is max of my Excel Formula/Macro skills, hence the question to edit
> More then 1 different products within 1 USERFORM PAGE for MULTIPLE entries within NEW ORDERS tab on pressing BUY

USERFORM: Tab > SALE. Combo Button > Sale. UserForm > NEW SALE.

and where to edit:
SALE r/c ViewCode. Forms > UserForm2 r/c VieCode, this copies all selections into SALES tab when BUY is pressed at end of userform.

Ultimately like to add Multiple purchase of "DIFFERENT" products within NEW SALE userform, like another EPOS TILL at any supermarket!

 

[Belleke]

Hi Stephan,
I gave it a try.
See attached.
Choose product and fill the form.
Push Enter Sale.
The sale will be added to the list.
When you finished click Finalise Sale.
List will be added to the sales sheet.

 

[Stephan]

Hi Stephan,
I gave it a try. See attached. Choose product and fill the form.
Push Enter Sale. The sale will be added to the list. When you finished click Finalise Sale. List will be added to the sales sheet.

Hello thank you for your time/effort, but this didn't work in Excel 2010, it quoted no such library "products.tbl" and there is NOT a "Finalise Sale" button, but the formatting does look different & macro is edited.

 

[Stephan]

Alternative version SALES#CATEGORY#.ZIP (1.1MB) now includes CATEGORY > PRODUCT seletion via USER FORM (TAB: NEW SALE > COMBO BUTTON: SALE > USERFORM: NEW SALE)

CATEGORY AZ > PRODUCT AZ > BARCODE is more practical for finding certain product instead of going through it all. This has been sorted in a new TAB: AZCAT using Formulas, it sorts lists to remove duplicates then sorts A-Z. To see all hidden tabs: Tools > Options > View > Sheet Tabs (Tick) (If any other hidden tabs, right tab > View Code > Sheet > Properties > Visible = Visible).

QUESTION: However how to edit DEFINED NAME AZPRODUZT to show only available selections?
in other words so bottom is NOT full of blank spaces hence up/down arrows only visible when number of options greater then TextBox/ComboBox Height.
How to edit this to work as intended in USER FORM TEXTBOX 2 (PRODUCT) Source is a defined named LIST called: AZPRODUCT

INSERT > DEFINE > NAME > AZPRODUCT:
=OFFSET(AZCAT!$O$2,0,0,COUNTA(AZCAT!$O:$O)-1,1)

Cheers, and suggestions of how to edit this USERFORM for MULTIPLE transactions?

 

[Belleke]

no such library "products.tbl

This has nothing to do with your excel version.
go to de VBA editor -> Tools -> References -> uncheck were it says Missing.

 

[Stephan]

Nothing to do with your excel version. Go to VBA editor -> Tools -> References -> uncheck Missing.

Hello Belleke, you're absolutely right! vba I just copy/paste/edit from examples.

Thank you for your great work that is exactly what I was looking for, with edit it now works download (0.9MB in XLSM): SALES#CODE#FINAL.zip

Question 1:
When your edit is saved to XLS (EXCEL 2003) UserForm2 isn't recognised, why is that? Can an edit sort this?
Download (1.1MB in XLS): SALES#CODE#FINAL.xls.zip
I'm more familiar with Excel 2003 Menu commands, Excel 2010 icon layout is dissimiliar to me.

Question 2:
In XLSM version after Sales are written a pop message to advise completed? Unsure where to put this into your revised UserForm2 vba:
MsgBox "Finalised Transaction written to SALES".

Question 3:
If you could edit my alternative CATEGORY VERSION to include your "Finalise edit" that would be very useful, as unsure how efficient my DataSort AZ code is:
Alternative version download > SALES#CATEGORY#.ZIP (1.1MB)

This includes CATEGORY > PRODUCT seletion via USER FORM (TAB: NEW SALE > COMBO BUTTON: SALE > USERFORM: NEW SALE).

Question 4:
On original edit SALES#CODE#.ZIP the USERFORM2 has a List Formula, but how to show only the relevant fields and not blank spaces?
USERFORM2 TEXTBOX 2 (PRODUCT) Source is a defined named LIST called: AZPRODUCT
INSERT > DEFINE > NAME > AZPRODUCT:
=OFFSET(AZCAT!$O$2,0,0,COUNTA(AZCAT!$O:$O)-1,1)

Cheers