VB - FUNCTION - REFRESH

CJ

Board Regular
Joined
Feb 22, 2002
Messages
77
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
Thanks for your offer of help have E-Mailed you a sample

Eagerly await your reply

CHRIS
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top