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
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
ASKER
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.
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.
So for the first part of your requirement the SalesRegister form should be used?
ASKER
Yes Norie
Reviewing. Have most of it working I think. Posting now so I don't lose track of this question.
ASKER
Thank you sir.
Made updates for each bullet item in original post except #5.
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
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
ASKER
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.
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.
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
ASKER
Hi buttersk,
Sorry for late reply.
I downloaded the last one, it doesn't want to load userform, it shows error 9.
Sorry for late reply.
I downloaded the last one, it doesn't want to load userform, it shows error 9.
Part of your original code contains this:
in routine : UserForm_Initialize
Set wbSales = Workbooks("SalesRegister.x
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.x
to this:
Set wbSales = ThisWorkbook
in routine : UserForm_Initialize
Set wbSales = Workbooks("SalesRegister.x
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.x
to this:
Set wbSales = ThisWorkbook
ASKER
Thanks buttersk,
Works perfectly, but it doesn't do auto numbering. its giving me error on below line:
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
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
Also updated to use reference of "ThisWorkbook as noted in previous comment.
SalesRegister.xlsm
'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
Also updated to use reference of "ThisWorkbook as noted in previous comment.
SalesRegister.xlsm
ASKER
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.
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.
Looks like we lost the formula's from the table in the October Tab.
I restored them.
SalesRegister.xlsm
I restored them.
SalesRegister.xlsm
ASKER
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?
Only one doubt, I think in Jan Tab, Sr. No. is not in the table.
Will you please check and confirm?
ASKER
One more thing, When you add product in order form, it picks only 09th Oct, can't it be today's date?
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
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
updated to set default value on OrderForm to todays date.
SalesRegister.xlsm
SalesRegister.xlsm
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everything works perfectly now......I am so so grateful....Hats Off to you.
Thanks a Million to make it possible.
Thanks a Million to make it possible.
ASKER
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.
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.
ASKER
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
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
Do you want to create 2 new forms for this?