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!!

 

[GlennUK]

I'd put it in a module and assign to a button called stock check, AND I'd put a call to the same routine from within the code for the passworded macro button.

 

[adamswalwell]

so therefore i would put into the macro code after the password code but before the end sub the code you have suggested and also put that on a macro button which will therefore do the same

 

[GlennUK]

Have the new code in a new module, named as "CheckStock" or whatever. Have a call to CheckStock in the button code, and also a call to CheckStock into your password code area before the End Sub. That way there is only one set of code that does the stock checking.

For the button, the code may look something like this:

Private Sub CommandButton1_Click()
    CheckStock
End Sub

And that reference to "CheckStock" calls your macro, and that's just the kind of thing you stick in your other macro too.

 

[adamswalwell]

After changing the code to fit in with all the information that i have (shown below) i am still recieveing a problem on the " If Worksheets(mysheets(myindex)).Range(c.Value & "StockTotal").Value < c.Offset(0, 1) Then" line below and i was wondering whether you could help:

Sub StockCheck()
mysheets = Array("Chips", "Fish", "Kebab", "Boxed and Single", "Burgers", "Pasties&Pies", "Drinks Other Items", "Southern Fried Chicken", "Packaging")

myranges = Array("Chip", "AllFish", "AllKebabs", "BoxedandSingle", "AllBurgers", "PastiesandPies", "AllDrinks", "Other", "SFC", "Packaging")

mystring = ""
For myindex = 0 To UBound(mysheets)
For Each c In Worksheets("Trigger Levels").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

 

[GlennUK]

Does it crash with an error, and then prompt for End/Debug? ( what's the error exactly ? )

If so, go into Debug mode, and use the Immediate Window to find out the values of things at that point in the code ( do View/Immediate if you can't see the Immediate Window ).

( to get information by using the Immediate Window, type
? c.Value
and press Enter )

 

[adamswalwell]

it comes to end/debug error

when i do as you say with the immediate window it gives the error object does not support this property or method!

is there any way i can get this document to you (over the internet) for you to see

 

[GlennUK]

Yes, you can ... although your error points to an object being referenced that does not exist ... for example, one the ranges being mispelled or not defined, as appear in your list.

Send it to Excelman at Lycos.co.uk and I'll have a look.

 

[adamswalwell]

will do!!