Date and Time Format

[adamswalwell]

I would like to put in an equation which will automatically (dependant on how my computers date and time is set) update itself.

I have tried =Now() but that only brings up the time and unless you re-calculate nothing else happens. I was thinking that maybe in control panel i could set it up but I have tried it and it won't happen.

Therefore if anybody knows anything about this please help!!

 

[adamswalwell]

OK!!

When I open the workbook there is no password!!! (The User Interface Sheet Opens)

From the User Interface I am able to enter the main menu by macro button which has the following password code on it:
Sub MainMenu()
'
' MainMenu Macro
' Access Main Menu
'

'
Sheets("Main Menu").Select
If Application.InputBox("PASSWORD PLEASE Please Note Password is case sensitive", "PASSWORD(case sensitive)", "")= "balsall" Then Exit Sub
Sheets("sheet1").Visible = True
Sheets("Main Menu").Select
End Sub

When the correct password is entered here this is where I would like a complete check of all the stock levels and the ones which are below a pre-set level will generate a warning window saying for example "Cod Stock Low. RE-ORDER"

I hope its not to confusing and that there is a way to which I can do this!!

 

[GlennUK]

So, you have code that's running from a button, it does a check of a password ... so that's where you put your check of the stock levels. Where are the stocks stored? ( which sheets, which ranges/cells ) What preset levels do you want? Have you thought about writing code for the checking yourself? I'm sure we'd all give you plenty of help.

 

[adamswalwell]

With it being a Chip Shop I have different stock levels for each different section on different sheets. E.G. on the Fish worksheet I have the stock levels of all fish and this is the same for the Pies and Pasties Worksheet I have the same for all the pies and pasties, then the same for all the other sections of the menu. I would like the pre-stock levels set differently for the different products say 50 for Chips but 20 for all the Fish.(surely I can change it for each different product check) The amount where each stock level is stored the cell has the name of the product for example for cod the cell name is "CodStockTotal"

Is there any other information you need?

 

[GlennUK]

Set up a single sheet with all your trigger levels for each product in each category. Have a list per sheet, and seperate item per product in the list. Have each item next to it's trigger level in the next cell. Name each list the same as it's corresponding sheet name.

When you've done that, paste a portion of the layout back on here, and we'll have a look, and tell you how to do your coding.

 

[adamswalwell]

Ok

I have done as you requested and now have all the products and my desired stock levels entered into a worksheet (within the same workbook). I have named each section of the sheet with the products from a worksheet in with:
Chips being Chip
Fish being AllFish
Kebabs being AllKebabs
Boxed and Single being BoxedandSingle
Burgers being AllBurgers
Pasties&Pies being PiesandPasties
Drinks being AllDrinks
Other Items being Other
Southern Fried Chicken being SFC
and
Packaging being Packaging

This is chips:
Chips
Chips 75

This is Fish:
Fish
Cod 50
Haddock 50
Plaice 50
Cod Roe 25
Fresh Roe 25
Scampi 25
Fish Nuggets 25
Fishcake 25
Scallop 50
Mini Fish 25

Drinks is the only different one:
DRINKS
Cans
Coca-Cola 25
Diet Coca-Cola 25
Fanta Orange 25
Fanta Lemon 25
Lemonade 25
Tango Orange 25

Shandy 25
Bottles (500ml)
Coca-Cola 25
Diet Coca-Cola 25
Fanta Orange 25
Fanta Lemon 25
Lemonade 25
Tango Orange 25
Bottles (1.5 ltr)
Coca-Cola 25
Diet Coca-Cola 25
Fanta Orange 25
Fanta Lemon 25
Lemonade 25
Tango Orange 25

Water 25
Juice Cartons
Orange 25
Apple 25
Polar Crush
Strawberry 25
Raspberry 25


I hope this is what you wanted.

If it is not cpuld you please explain in more detail?

 

[adamswalwell]

The way this has come up on screen is different

In the far left hand column of each section of the table there is the worksheets name

then the next column houses the product name (drinks this is different it houses the drink sizes/manufacturers) then the next column houses the desired trigger level

 

[GlennUK]

Just a couple more queries ... when you say Fish being AllFish, etc etc ... it suggests you've got different named areas compared to the sheet names. Is it "sheetname" is "rangename" that you've told me there?

Can you also tell me what you've done for Stock Total cell names where the stock item has an embedded space ... for example "Cod Roe" cannot have a stock total cell name of "Cod RoeStockTotal" ... what is it? Is the convention you've chosen consistent for all the stock names in the workbook?

 

[adamswalwell]

The word before the word being is the Word being is the worksheet name and the AllFish etc is the name of the range to which the table the products of fish are enclosed in.

Where I have set up for the stock totals to be input each product for example Cod Roe is in the left hand column on its seperate worksheet and then where the total of that stock is the name of the cells correspond with Cod being CodStockTotal and Haddock, HaddockStockTotal and so on apart from TangoOrange1.5ltrDrinks where it is TangoOrange1.5ltrStockTotal and the same applies for the 500ml bottles where it is TangoOrange500mlStockTotal where it should have been TangoOrange1.5ltrBottleStockTotal and TangoOrange500mlBottleStockTotal

Anything Else?

 

[GlennUK]

Don't get it all

I don't all of what you're saying, but I wrote this which works on a guess at what you've got, done for 3 sheets ( that's all I have time to create for an example ):

Sub CheckStock()
    mysheets = Array("Fish", "Kebabs", "Pasties&Pies")
    
    myranges = Array("AllFish", "AllKebabs", "PastiesandPies")

    mystring = ""
    For myindex = 0 To UBound(mysheets)
        For Each c In Worksheets("RestockLevels").Range(myranges(myindex))
            If Worksheets(mysheets(myindex)).Range(c.Value & "StockTotal").Value < c.Offset(0, 1) Then
                mystring = mystring & mysheets(myindex) & " : " & c.Value & " below " & c.Offset(0, 1).Value & Chr(10)
            End If
        Next
    Next
 
    If mystring <> "" Then MsgBox mystring
            
            
End Sub

 

[adamswalwell]

ok thanks i'll have a go though at the moment it seems confusing

thanks for your help

if there are any further problems i'll be sure to return.