The screen shot above shows my add new supplier worksheet. As you can see the sheet includes three fields these are suppliers name, phone and email. The end user enters information in these field manually, then uses the add supplier macro to transfer this data to the supplier sheet database. Also on the sheet is a main menu macro button.
The add new stock sheet allows the end user to enter data in the fields above, the user can enter one size or a variety of sizes. The information the user enters is transferred to the stock database. The sheet consists of two macro buttons these are new stock sml and main menu.
The screen shot above shows the delete stock sheet, which the user can delete any of his current stock from the database. The sheet includes a drop down list which allows the user to select which stock he wants to delete, from the database. He then selects delete stock this will completely remove it from the system. On the sheet are two macro buttons these are delete stock and main menu.
The user then enters its new details about quantity and price. The following information is then transferred and adjusted in the stock database, by the use of the edit stock macro button. There is also a main menu button, the user requirements are met.
This screen above shows edit stock sheet, which allows the user to edit any of his current stock. I have included data validation in the form of a drop down list. When the user selects an item a number is shown next to it, corresponding to where it is in the stock database.
In the screen above my supplier database, I have used format cells text. This allows all phone numbers entered to have a zero in front, otherwise only numbers will be shown as above.
The screen shot shows that formatting numbers with text allows the zero to be in front. So the user can understand what county code belongs to which number.
I am using a validation so that I can only enter a maximum of 11 digits. The validation means if the user enters more than 11 digits, this will be incorrect thus
Showing an error message. Indicating more than 11 digits have been entered.
The screen above shows the validation in process, I have entered more than 11 digits and tried entering it. A message is being shown indicating that the number is invalid, and I will have to re enter.
The sheet above called weekly sales sheet shows the total amount of items the user has sold during a period of a week. It does this by using a sum function, by multiplying the price by the quantity. Then a total price is shown, also on the sheet is a print screen macro this allows the user to print off a record of transactions customers have made during the week. Another macro includes a save function which allows the user to save the sheet and view it later. The information from the weekly sales is used to compare weekly sales trend. Other macro buttons on the sheet include main menu.
The screen above shows a validation in which I can only enter a maximum of 50 of each quantity sold. I have used a maximum of 50 because the user will not order more than 50 of any items. It is also an unrealistic number for the quantity sold.
The screen above shows the formulae page for my weekly sales. It shows the formulae I have used to calculate the total price. Which is =sum (h3:h18).
In the add new stock sheet above a validation has been inputted, so that when the user enters an item name. He can only enter a maximum of 50 letters; this is because it would be unrealistic for a name to be 50 characters long.
The screen above shows me creating a validation so that a drop down can be created, so the user can select easily which item he wants. First I had to name all my items in the stock database called item by cell naming.
Then when I created my validation I had to refer back to the name item, in order to create a drop down list.
In the sheet above I used a vlookup function, which allowed to me to look up the correct phone number in the array table for that supplier.
The end results after lookup the stock database for the phone number is that the number should appear as shown on the screen.
In the screen shot below I will use conditional formatting, to highlight certain stock which has gone below the expected level of three. If the stock in a certain size has gone below four an error message is shown to update the stock.
EDIT STOCK MACRO:
Sub edit stock ()
'
‘Edit stock Macro
‘Macro recorded 05/02/2008 by Tarif
'
Range ("H14").Select
Selection. Copy
Sheets ("Stock sheet (database)").Select
Range ("B11”).Select this is the original code
Range ("B" & Range ("stkrow").Value).Select
Selection.PasteSpecial Paste: =xlPasteValuesAndNumberFormats, Operation: = _
XlNone, SkipBlanks: =False, Transpose: =False
Sheets ("Edit stock").Select
Range ("H9").Select
Application.CutCopyMode = False
Selection. Copy
Sheets ("Stock sheet (database)").Select
Range ("C" & Range ("stkrow").Value).Select
Selection.PasteSpecial Paste: =xlPasteValuesAndNumberFormats, Operation: = _
XlNone, SkipBlanks: =False, Transpose: =False
Sheets ("Edit stock").Select
Range ("H10").Select
Application.CutCopyMode = False
Selection. Copy
Sheets ("Stock sheet (database)").Select
Range ("D" & Range ("stkrow").Value).Select
Selection.PasteSpecial Paste: =xlPasteValuesAndNumberFormats, Operation: = _
XlNone, SkipBlanks: =False, Transpose: =False
Sheets ("Edit stock").Select
Range ("H11").Select
Application.CutCopyMode = False
Selection. Copy
Sheets ("Stock sheet (database)").Select
Range ("E" & Range ("stkrow").Value).Select
Selection.PasteSpecial Paste: =xlPasteValuesAndNumberFormats, Operation: = _
XlNone, SkipBlanks: =False, Transpose: =False
Sheets ("Edit stock").Select
Range ("H9:I11").Select
Range ("I9").Activate
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range ("H11").Select
ActiveCell.FormulaR1C1 = ""
Range ("H10").Select
ActiveCell.FormulaR1C1 = ""
Range ("H9").Select
ActiveCell.FormulaR1C1 = ""
Range ("H14").Select
ActiveCell.FormulaR1C1 = ""
Range ("I27").Select
End Sub
Add new stock macro
Sub newstocksml()
'
' newstocksml Macro
' Macro recorded 07/02/2008 by Tarif
'
'
Sheets("Stock sheet (database)").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Sheets("Add new stock").Select
Range("D4").Select
Selection.Copy
Sheets("Stock sheet (database)").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Add new stock").Select
Range("D10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock sheet (database)").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Add new stock").Select
Range("D7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock sheet (database)").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Add new stock").Select
Range("G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock sheet (database)").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Add new stock").Select
Range("K7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Stock sheet (database)").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Add new stock").Select
Range("D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("D7").Select
ActiveCell.FormulaR1C1 = ""
Range("G7").Select
ActiveCell.FormulaR1C1 = ""
Range("K7").Select
ActiveCell.FormulaR1C1 = ""
Range("D10").Select
ActiveCell.FormulaR1C1 = ""
Range("H11").Select
End Sub
Delete Stock Macro:
Sub delete ()
'
‘Delete Macro
‘Macro recorded 30/01/2008 by Tarif
'
'
Sheets ("Stock sheet (database)").Select
Rows ("4:4").Select
Selection. Delete Shift: =xlUp
End Sub
Save Sheet Macro:
Sub save()
'
' save Macro
' Macro recorded 30/01/2008 by Tarif
'
'
ActiveWorkbook.save
End Sub
Main Menu Macro:
Add new Supplier Macro:
Sub ss()
'
' ss Macro
' Macro recorded 29/01/2008 by default user
'
'
Sheets("My suppliers sheet").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Sheets("Add new supplier").Select
Range("C4").Select
Selection.Copy
Sheets("My suppliers sheet").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
Sheets("Add new supplier").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("My suppliers sheet").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
Range("D4").Select
Sheets("Add new supplier").Select
Range("C8").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.CutCopyMode = False
Selection.Copy
Sheets("My suppliers sheet").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
Range("D15").Select
Sheets("Add new supplier").Select
Range("C4:C13").Select
Range("C13").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("D16").Select
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "ss"
End Sub
Main Menu Macro:
Sub ga()
'
' ga Macro
' Macro recorded 29/01/2008 by default user
'
'
Sheets("Add new supplier").Select
End Sub
Sub mian()
'
' mian Macro
' Macro recorded 29/01/2008 by default user
'
Sheets ("Elite main menu").Select
End Sub
Print Screen Macro:
Sub pprin()
'
' pprin Macro
' Macro recorded 30/01/2008 by Tarif
'
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub