• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform too much loaded

M

Mukhtar

New Member
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 (It is the name of Yarns)
The colours are 1 to 20 ( It is the name of colors)

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 help,
Regards,
Mukhtarhope
 

Attachments

Hi ,

Can you explain in more detail the data entry process ; how will a user enter all the data ? What will be the sequence of data entry ?

When will a record be termed complete , and transferred to the worksheet ?

Please explain with reference to your process , and not with reference to your userform.

Narayan
 
Dear Narayan,

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. As you had asked this question.

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
 

Attachments

Hi ,

I am confused ; what does this file have anything to do with your initial post of 880 textboxes ?

Or is this now a new problem , while your initial one is resolved ?

Narayan
 
Dear Mr.Narayan,
My initial question is not solved,
My previous file and present file are both same.
My previous file was in expanded form and now it is in reduced form.

I will put it down like this.
I have 11 categories of Cloth yarn.
Each one has 20 colours, therefore there are 11 x 20 = 220 combinations.
Each one has 4 textboxes (No of Rolls, Weight, Price & Amount) so 4 x 220 = 880. As there were 880 Textboxes. Everytime excel used to crash saying, out of memory.

I sat for 2 day and changed the userform like this. I entered the 11 categories into 1 combobox and 20 colours into another combobox. and left over were 4 Textboxes i.e. 1) No of Rolls, 2) weight of Rolls, 3) Price of Rolls, 4) Total Amount.

These yarns come in Roll form (Cloth come in Rolls around a wooden stick).
So how many Rolls sold or bought is calculated. Each Roll has weight In Kgs.Gms. So Price is calculated based on Weight per Kg.gms and not Rolls.
Amount Total is weight x Price.

I reduced the Number of Textboxes by putting the 11 Categories into 1 combobox and 20 colours into another Comboboxes.

It is like this, I buy or sell 11 categories of Yarn which come in 20 colours.
They come in Roll form and have weight.
So I calculate Number of Rolls, Then weight, Then Price and the Total Amount based on Weight x Price.

In the Read me sheet I have given everything in detail.

11 Categories of yarn
20 colours for each
Comes in Roll form and weight
Create Calculations
No of Rolls
Weight of Total Rolls.
Price of Total Weight
Amount (Weight x Price)

Purchase Stock

Duplicating Purchase Stock account file as
Sales stock.

First 6 textboxes contain info of seller
Date, Name, Mobile, GST, Pand card & Aadhar card No..

Person selects 1st combobox of category and selects type of yarn.
Again selects 2nd Combobox of colours. The fills Rolls textbox with no of Rolls, Next enters Weight of Rolls, Next fills 3rd Textbox with Price. 4 TextBox is not filled amanually, but is automatically filled with amount from calculation by weight into Price.

In the worksheet "PData" After the first 6 coloumns, The first category contains 80 columns 4 x 20 (Rolls, Weight, Price & Amount) X 20 Colours.
Next 80 columns for Next Category, 3rd 80 columns for another category and so on till last category.

First Category and colours are selected and data entry is made for multiple selection in the same row by using "Add Data" Command button (Without using (xlUp) means not moving to next row.
After all selections are made in the same row The info of the customer or seller in the first textboxes is filled (Date, Name, Mobile, GST, Pancard, Aadhar card no) and then the second command button " Submit Data' is clicked for new entry into new next row. (Using xlup) Here the process is finished.

I hope so, I have made it easy.

Regards,
Mukhtar
 
Hi ,

Let us take it step by step.

You have 11 categories of yarn ; each category can come in 20 colours.

This gives a total of 220 combinations of yarn type and colour.

Each combination has 4 value attributes of number of rolls , weight , price and amount.

I have a few doubts :

1. Is a user going to purchase / sell all 220 combinations every time ?

If not , then what is the maximum number of combinations that a user will enter ? Surely if we take that one combination and its associated value attribute entry will take not less than a minute , 220 combinations will take nearly 4 hours !

2. What is the actual use that will be made of this data ? What is the further data processing that will done ? The data should be stored in the worksheet / workbook in a way that makes further processing easier.

Narayan
 
Dear Mr Narayan.

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

Regards,
Mukhtar
 

Attachments

You must log in or register to reply here.
Back
Top