Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VB - FUNCTION - REFRESH

  1. #1
    Board Regular CJ's Avatar
    Join Date
    Feb 2002
    Location
    CHESTER, ENGLAND
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    Guest

    Default

    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.


  5. #5
    Board Regular CJ's Avatar
    Join Date
    Feb 2002
    Location
    CHESTER, ENGLAND
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    Board Regular CJ's Avatar
    Join Date
    Feb 2002
    Location
    CHESTER, ENGLAND
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Eagerly await your reply

    CHRIS

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •