VB - FUNCTION - REFRESH

[CJ]

I came across this site during a Internet [Excel-help/tips search] I have found it very helpful & have learnt a lot just reading through the posting.

Average Excel knowledge - beginners VB knowledge

I have a Excel / VB Type problem which I need some help with!

I have a Time Sheet, cell A6 holds Weekly Hrs, this it’s value is referenced in a hidden a row (20) then it’s value is divided by 10 ie 3:42hrs in B20 The value of B20 is referenced in all of the 31 VB functions built into this Time Sheet. The Time Sheet is issued to users with 37:00hrs in A6, if a user works part-time say 25:00hrs if they type in 25:00hrs in A6 the cells on the Time Sheet the cells are not refreshed, to their new value. If I copy & paste the default value ???? back into the cells the values are refreshed
Is there any way I can get the cells to refresh? after a new value is entered into A6!!!
Preferably I would like

1) A line in my VB Function to refresh the values
or
2) A button on Aprils Time Sheet which when pressed will re-run all 31 functions, which will result in all cell being refreshed!!

If option 2 is required I need detailed HELP

CHRIS

[Jack in the UK]

Hi mate

refresh......???? do you mean VBA calculate which s the same as F9 on demand..

I always turm cal off after veriables and back o at end and save as last, some times cereat back up at start or finish...

UNLESS the script DEMENDS cal to be on say my ALL excel mail merge must be on or formula VLOOKUP will fail to change..

Add line to cal.....

Your UK based mate please keep in touch, use private message ill reply

[Tom Urtis]

If all your calculations are based on the changing of data in cell A6, then try placing this code in your worksheet module.

First though, see if your Calculation mode is set to Manual (go to Tools > Options > Calculation tab). If so, select the Automatic option.

If for some reason your workbook is being forced onto Manual Calculation mode (maybe due to some other code), then right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$6" Or Target.Cells.Count > 1 Then Exit Sub
Calculate
End Sub

Any help?

Hi

You have already go some good information from Jack and Tom. All I would add is that Excel should automatically recalculate each time a cell it's referencing changes. Unless, as Toim sais, you have Calculation in manual.

You didn't mention it, but are these Functions custom ones? If so you could place:

Application.Volatile

as the first line of code, but I would suggest only doing this if really needed as too many Volatile Custom functions will have an adverse effect a re-calcultions.

[CJ]

Thanks for the info,
But none of it has been helpful
Automatic Calculation option is on, have checked & also all standard formulas work OK
when A6 is changed. The funtions that do recalculate are the CUSTOM FUNCTIONS. Sample funtion below:

Function sick51(c9, d9)
If Cells(9, "C") = "SICK" Or Cells(9, "C") = "HOL" Or Cells(9, "C") = "STUDY" Or Cells(9, "C") = "BANK" Or Cells(9, "C") = "COMP" Or Cells(9, "C") = "????" Then
sick51 = Cells(20, "B")
ElseIf Cells(9, "C") = "FLEXI" Or Cells(9, "C") = "UN-PAY" Then
sick51 = 0
Else: sick51 = Cells(9, "d") - Cells(9, "C")
End If
End Function (first 3 lines are actually on the 1st line)

C D E
8 In Out Hours
9 ???? ???? 03:42
10 ???? ???? 03:42
11 ???? ???? 03:42
12 ???? ???? 03:42

All 51 funtions are spread over 6 modules
Hope this further info helps someone try and solve this problem for me.

Thanks
CHRIS


_________________


[ This Message was edited by: CJ on 2002-03-06 14:37 ]

[ This Message was edited by: CJ on 2002-03-06 14:38 ]

[Jack in the UK]

Hi Chris

Sory you struggling, can yoyu email me something on
JackintheUK@hotmail.com

I spend some quality time on it for you, take the confedentila stuff out all i need is te active stuff and make up if nane addresses just one line us me or something, and ill add as i need to

Ill do all i can,

[CJ]

Thanks for your offer of help have E-Mailed you a sample

Eagerly await your reply

CHRIS