Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Cells (2003)

    In a spreadsheet designed to total vehicle sales throughout the month, I'm trying to work it so that a monthly total is updated automatically after a user enters the daily amount.

    Cells D14:E14 (daily sales) are merged
    Cells D15:E15 (monthly sales) are merged

    (I'm not sure if the fact that the cells are merged make a difference, but I felt it should be mentioned.)

    If, on the 1st, 5 is entered into D14, then 5 should automatically be entered into E14
    If, on the 2nd, 3 is entered into D14, then 8 should automatically be entered into E14 (5 for the first day + 3 for the second day)
    If, on the 3rd, 9 is entered into D14, then 17 should automatically be entered into E14 (8 for the first two days + 9 for the third day)
    Etc....

    This calculation should take place as after D14 has been updated

    I've tried working something out with a macro, but it really should be an automated process. I've also attempted using VB, but each time I enter any coding that appears to work, it seems to get caught up in some sort of loop that occurs when updating ANY cell in the spreadsheet. (My VB experience is limited.)

    Now, although I've only mention car sales, there are a total of four separate cells that require the same type of automated calculation (car sales [D14:E14], truck sales [F14:G14], used vehicle sales [H14:I14] & special finance sales [J14:K14]).

    Can someone assist me in working through this problem?

    Eric

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Cells (2003)

    I'm sorry, I don't understand. How can anything be entered in E14 if D14 and E14 are merged? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Apart from that, this doesn't seem a good idea to me. You have no check at all that the total is correct, since each day's data overwrites that for the previous day. You should use a separate cell for each day's data, plus a cell for the monthly total.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update Cells (2003)

    If the cells are not merged then this should work.
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim rCell As Range
    Set rng = Union(Range("D14"), Range("F14"), _
    Range("H14"), Range("J14"))
    Set rCell = Intersect(Target, rng)
    If Not rCell Is Nothing Then
    Application.EnableEvents = False
    With rCell.Offset(0, 1)
    .Value = .Value + rCell.Value
    End With
    Application.EnableEvents = True
    End If
    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    if they are merged, then the offset can not contain a value...

    Steve

  4. #4
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cells (2003)

    Hans...

    In answer to your confusion (as well as everyone who read my original post), I mis-typed. It SHOULD have been if a number is entered into the merged range D14:E14, a calculated total would automatically be entered into the merged range D15:E15. (And I tried so diligently to ensure there were no typos. <shaking head>) My apologies.

    As far as your comment about there being no accuracy check, this is true...and something I'll have to mention to the person initiating the request.

    Steve...

    Your code worked perfectly...and it worked with the merged cells. As I mentioned above, I mis-typed. I first un-merged the cells and found that it did indeed work with the ranges I originally gave. I simply re-merged the cells and modified the Offset value to (1, 0) and it worked like a charm.

    Thanks to both for your quick responses!

    Eric

  5. #5
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cells (2003)

    Well...it worked until I realized that it wasn't able to do the automatic calculation because the worksheet is protected with a password. (I neglected to mention this because the normal formulas [e.g. Sum(L3:L5)] in certain cells weren't affected by the protection. I didn't realize that VB would be hindered by the password. Anyway...

    I attempted to add

    ActiveSheet.Unprotect Password:="PASSWORD"

    at the beginning of the sub and

    ActiveSheet.Protect Password:="PASSWORD"

    at the end of the sub, but no good. Sooooo........

    With a protected worksheet, how can I utilize the wonderful code you so generously gave me?

    Eric

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update Cells (2003)

    What you propose should also work. What doesn't work about it?

    Another way is to add something like this macro to the thisworkbook object

    <pre>Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect _
    UserInterfaceOnly:=True, _
    Password:="swordfish"
    End Sub</pre>


    It will allow the macros to change the contents without having to unprotect the sheet.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Cells (2003)

    Did you unlock the affected cells?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update Cells (2003)

    I presumed he wanted the cells that he was using to "totalize" (row 15) to be protected, but still allow the macro to change them.

    Steve

  9. #9
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cells (2003)

    I now know that what I tried (ActiveSheet.Unprotect Password:="PASSWORD" at the beginning & ActiveSheet.Protect Password:="PASSWORD" at the end) didn't work for the same reason your next proposal doesn't work: it's a shared workbook. Apparently, attempting to unprotect a shared workbook returns the error message:

    Run-time error '1004':
    Unprotect method of Worksheet class failed

    Once again, I realize belatedly that I should have mentioned some aspect of the situation but failed to take it into consideration as a possible hindrance. Sooooooo...

    Is there a way to update a protected, shared workbook using VB?

    Eric

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Cells (2003)

    Just a warning: sharing a workbook can easily lead to corruption in the workbook. It is a feature best avoided.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Cells (2003)

    A word of warning about Steve's code, and back to Hans' original point. If the user enters the incorrect daily sales amount, how are you going to correct the monthly amount? From an accuracy point of view, this is a very poor design!
    Legare Coleman

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update Cells (2003)

    The protection on a shared workbook can not be changed: that is "by design"

    A workaround would be to store the values you want to have the macro change on an uprotected hidden workbook and have the cells in the protected sheet refer to the other cells. The formulas will be updated even if it is locked and protected.

    Steve

Posting Permissions

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