Sales & Inventory Management System

Hello Experts,

I have created subject system in excel, I want to make it automated through userform. Since one week I m working very hard, now my head is aching of thinking of the repeated errors.

I need your help very badly.

My Requirement in attached sheets are:

1. ProductCode Combo Box drop down Value must be from the sheet "Master Inventory" Column "B" excluding header till the last row.
2. As I will be able to select Product Code, its available quantity which is in Column "N" in the same sheet, must shown in AvlbleQnty Label Box.
3. QntyTextBox must accept only the quantity lesser than the available quantity which is AvlbleQnty Label Box.
4. Whichever ProductCode's Available Quantity is less than 5, I need a Command Prompt "Would you like to order this Product Now or Later", If Ans is Yes, it must pop up another User Form "OrderForm", where I can input Date(CalenderFrm popup), Product Code, Supplier Code, Description, Size & Qnty. (I haven't created such UserForm yet in attached sheet). After Clicking Add button, it must update in the last row in OrderForm Sheet.
5. Discount Combo Box must be drop down only for the "Supplier", which I may select from TermComboBox.

Please help....Thanking you in Advance.

Kind Regards,
Shums
SalesRegister.xlsm
LVL 31
ShumsDistinguished Expert - 2017Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieData ProcessorCommented:
Shums

Do you want to create 2 new forms for this?
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

Thanks once again. I was wishing, if you can attend this query as you know what exactly I need.

No Norie, I just want to create one new form for Ordering & one Command Prompt.
0
NorieData ProcessorCommented:
So for the first part of your requirement the SalesRegister form should be used?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ShumsDistinguished Expert - 2017Author Commented:
Yes Norie
0
Ken ButtersCommented:
Reviewing.   Have most of it working I think.  Posting now so I don't lose track of this question.
0
ShumsDistinguished Expert - 2017Author Commented:
Thank you sir.
0
Ken ButtersCommented:
Made updates for each bullet item in original post except #5.

5. Discount Combo Box must be drop down only for the "Supplier", which I may select from TermComboBox.

Where data for discount combo box comes from is not clear to me.

Changes Summary  :

I changed your tables on the master invetory sheet and the orderform sheet to excel Tables.  I believe that allows for easier referencing in the VBA code.

Changed the assigned macro on Sales button to open the form, and removed the hyperlink so that clicking the button would open the form.

for Item #1 :  Look at rowsource in the form.  That defines what will populate in the combobox.

for Item #2 : this was partly coded.  I added some error checking that was causing form display to fail.

for Item #3 : this was partly coded.  I modified code to account for the fact that the data is being retrieved from an Excel Table now.

for Item #4 : Completed with new code.
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Bravo Buttersk,

Works perfectly.

Sir,

I would like to add some more things:

1. After adding quantities to order form, it must automatically show up in available quantity.
2. After adding quantities to order form, Order Form UserForm must unload.
3. Discount drop down; initially I added items in Userform initialization, then I removed it as it was dropping down to any terms I select from TermComboBox, I would like it to drop down only if I select "Supplier" in TermComboBox. Discount must be "", "5%" & "10%".

I have another request add to this system, please check link below:
One Userform For Multiple Sheets

I would like to have above link UserForm too in this system, so that I can view reports too. If you can do this then I am completely done.

Once again I am very grateful for your effort. Truly appreciated.
0
Ken ButtersCommented:
I would like to add some more things:

Summary :

Item 1 : Adding quantities to order form.  The way you have your spreadsheet set up, adding quantities to order form does nothing for available quantity.  the Order Form table is disconnected from all other formula's.  In order to get this to work, I had to add the additional information to the appropriate Month Tab.   This took quite a bit of rework to get this to work.  -- [COMPLETE]

Item 2:  Order Form now unloads.  [COMPLETE]

Item 3 Discount Dropdown is now populated with 5 or 10% only if there is supplier in TermComboBox. [COMPLETE]

as far as your referral to the other question, I don't think I would care to pursue any further changes under this question.   It has already taken me quite a few hours to get this working as it is.

You can feel free to open a new question with additional work, or ask another expert if they want to pick it up from here.

If I have coded something incorrectly based on what you have already asked for, I would be willing to review any issues with that.

Thanks!
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Hi buttersk,

Sorry for late reply.

I downloaded the last one, it doesn't want to load userform, it shows error 9.
0
Ken ButtersCommented:
Part of your original code contains this:

in routine : UserForm_Initialize
Set wbSales = Workbooks("SalesRegister.xlsm")  ' This Workbook

Take note the name of the workbook.

"SalesRegister.xlsm".

When you downloaded a new copy of the workbook... make sure that it did not rename it  as "SalesRegister(1).xlsm or something similar.

To avoid this error and no longer be dependent upon using the same workbook name ... that command in the "UserForm_Initialize" can be replaced.

from this :
Set wbSales = Workbooks("SalesRegister.xlsm")  ' This Workbook

to this:
Set wbSales = ThisWorkbook
0
ShumsDistinguished Expert - 2017Author Commented:
Thanks buttersk,

Works perfectly, but it doesn't do auto numbering. its giving me error on below line:

lrow = ws.Cells(Rows.Count, 2).End(xlUp).Row

Open in new window

0
Ken ButtersCommented:
Replaced this code:

        'Add Function
'        Range("B2").Select
'        lrow = ws.Cells(Rows.Count, 2).End(xlUp).Row
'        Set myrange = Range(ws.Cells(2, 2), ws.Cells(lrow, 2))
'        For Each cell In myrange
'        cell.Offset(0, -1).Value = i + 1
'        i = i + 1
'        Next cell

With this code :

   newRow.Range.Cells(1, monthTable.ListColumns("SR. No.").Index).Value = newRow.Range.Row - 1

Also updated to use reference of "ThisWorkbook as noted in previous comment.
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Buttersk,

It doesn't drag formulas in new rows in the table for Unit Price, CP_Amt, SP, SP_Amt, Final Price, Current Inventory......as a result OrderForm quantity which adds up as purchase doesn't reflects available quantity.
0
Ken ButtersCommented:
Looks like we lost the formula's from the table in the October Tab.

I restored them.
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Thanks a lot Buttersk,

Only one doubt, I think in Jan Tab, Sr. No. is not in the table.

Will you please check and confirm?
0
ShumsDistinguished Expert - 2017Author Commented:
One more thing, When you add product in order form, it picks only 09th Oct, can't it be today's date?
0
Ken ButtersCommented:
You are correct.

Here are steps to correct this:

1) select Jan tab
2) Now at the in the main Ribbon you should see a tab that says "Table Tools / Design"
3) select that tab
4) On the far left of the ribbon you will see table name as "JanTable".  under than is a button called Resize Table.

Click that button and change the value in the pop up box from $B$1:$P$2 to $A$1:$P$2.
This will include Sr. No. in the table.

Also have attached file where I made that change.
SalesRegister.xlsm
0
Ken ButtersCommented:
updated to set default value on OrderForm to todays date.
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Now Everything looks perfect......

Just one last favor, where you select Product Code; If I know the code and will it not drop down the list as I type. Currently if I type the code and press tab button on keyboard, its shows calender userform, then I need to cancel and I have to scroll down to select the code.
0
Ken ButtersCommented:
couple of things changed to correct this.

1) needed to change the event that fires when updating product code on the SalesRegister form.   Before this change you could not enter 66-2736-212 because it would always match on 66-2736-210 before it would let you type the last digit.

As a result, the subroutine now only fires after you leave the cell.

2) Changed the tab order of the items on SalesRegister form, so that a tab keypress will work from the top of the form to the bottom of the form.   prior to this change tabbing out of product Code, would take you to the date field next... which is why the calendar popped up.
SalesRegister.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Author Commented:
Everything works perfectly now......I am so so grateful....Hats Off to you.

Thanks a Million to make it possible.
0
ShumsDistinguished Expert - 2017Author Commented:
Buttersk,

Please review below link and help me there too, I will be very grateful to you.

One Userform For Multiple Sheets

I did asked it separately.
0
ShumsDistinguished Expert - 2017Author Commented:
Buttersk,

I need your help again, please see attached....Looks like we lost the formula's from the table in the Mar, Apr, May, Jun, Jul, Aug, Nov & Dec Tab.
SalesRegister-11.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.