Userform too much loaded

[mukhtarhope]

Dear Forum Members,

I need a little help with an userform with fewer boxes

I am trying to create an inventory.

I sell yarns (clothes for school children)

I have 11 categories of Yarn Listed Alphabets "A" to "K".
Each Category has 20 colours.
So we have combinations of 11 categories x 20 colours = 220 combinations
Each Yarm comes in Roll form (So how many Rolls sold or bought)
Each Roll has a weight in Kgs.Gms (Kilograms)
Price at which bought or sold
Amount = Weight x Price (not Roll into Price)
In Each Category, 20 boxes for Rolls for each colour, 20 boxes for Weight of each colour, 20 boxes for Price for each colour, 20 boxes for Total Amount for each colour (Weight x Price)
(Roll, Weight, Price, Amount) 20 x 4=80 Text boxes in Each Category
Therefore for 11 Categories, it is 880 Textboxes
This for the Purchase section
Again the same type for the Sales section
So the number of Textboxes for input increases to 880 x 2 = 1760

I tried making Userform with codes, but when I reach halfway Excel crashes and says, overcrossing 64k, or out of memory reduce data, or says Procedure to long.
How should I reduce the number of Textboxes and codes or somebody shows me a designed userform on how I can overcome it.

The category is A to K
The colours are 1 to 20

Waiting for help,
Regards,
Mukhtarhope

[SamT]

Use three ListBoxes to show/select Categories, Colors, and weights.

Use three TextBoxes to display selected Category, Color and weight

[mukhtarhope]

Dear Members,

I forgot to add a document, it contains the image of the userforms that I created.
Attached is an image of the userforms which I created in 2 methods (Combobox method and Userform Multipage method, But both failed as out of memory or too much Textboxes saying reduce data by Excel). It contains the real names of Yarn and colours.
how I can overcome it with fewer textboxes. A buyer or purchaser might buy or sell multiple categories and multiple colours. So there might be multiple selections when filling data before the submit button is pressed.

Waiting for reply,
Regards,
Mukhtarhope

[SamT]

We need to see the Excel workbook. Here might need to be changes made in the layout to make it possible to code a UserForm.

[mukhtarhope]

Dear SamT,


I am uploading the original file, Please read the Read me sheet in which I have clearly described everthing. The Main working sheet is "PData". The userform is also in it you can find it on "PData".
My Data entry starts from Row 6.
The Command button1 code is there and works properly but does not start entry from Row 6, need correction.
There are 2 frames.
I.D section (Frame1)contains Input Info about seller.
Data input Section (Frame2) contain 2 Comboboxes and 4 textboxes for entry with "Add Data" command button, which will add data in same row but not enter next row.
After all entries completed with Add Data button, Then only Click Submit Data Command button in upper frame to move on to next row.
The read Me sheet is fully described and explained how the process goes.
I am little better at normal Excel but weak in VBA. Still learning.


Thanking you for your reply
Regards,
Mukhtar

[mukhtarhope]

Dear SamT,
Here I am uploading a sample sheet with only 3 Categories with 3 colour to understand easily with dummy Data.
For every new Name of Seller a new row is used.
Please check this sheet and answer back.

Regards,
Mukhtarhope

[SamT]

I am working on something now. Maybe by monday.

[SamT]

The attached is merely an example of how to code worksheets so the actual working code and UserForms are very easy to code to use the information on the sheets.

I suspect that your current method of ordering is difficult since the order person must be familiar with every supplier's product lines or must o a lot of research while filling out the User Form.

I designed the attached based strictly on the info you provided, but would really like to give you a much better system.

I suspect that most categories and colours are unique to each supplier. And I would recommend a different product line sheet for each supplier. The sheets should have at least four tables:

1. Supplier info
2. Price, by category and colour
3. Weights, also by category and colour
4. Prices, also by category and colour

The sheets should be named by Supplier Name.