Results 1 to 11 of 11
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Default / Paste Problem (2002)

    I have the user enter time in cells A1:A10 one at a time. In B1:B10 I have the change event add an hour to the cell in column A and place the result in column B as a default. The user can change the default if he wishes.

    My problem occurs when the user, let
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default / Paste Problem (2002)

    Why use a change event rather than just having a formula in column B that adds the hour?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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: Default / Paste Problem (2002)

    Slight modification to Jan's suggestion:
    Why not just enter times in A,
    Hour increases in B
    and in C use the formula(eg in C2)
    <pre>=A2+B2/24</pre>


    Formulas can be copied down as desired.

    If you want no display until A has a time, you could use something like this in C2
    <pre>=IF(A2,A2+B2/24,"")</pre>

    and copy it down the column

    Steve

  4. #4
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Default / Paste Problem (2002)

    One reason I am using the change event instead of a formula is to preserve the calculation for future use. For instance, A3 has 13:00. B3 would have 14:00 based on a formula in cell B3 like =A3+Time(1,0,0). The user then decides he doesn
    You know it's time to diet when you push away from the table and the table moves.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Default / Paste Problem (2002)

    Hi Steve,

    Thanks for the reply. My reply to Jan's post may shed some more light on what it is I am trying to do.
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Default / Paste Problem (2002)

    You could use code something like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A:A"))
    If Intersect(Target, Range("B" & oCell.Row)) Is Nothing Then
    oCell.Offset(0, 1).Value = oCell.Value + (1 / 24)
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Default / Paste Problem (2002)

    Thanks for the reply. I have code very similar to yours. The main problem I'm having is when a value gets copied from A3:B3 and then pasted into row A4:B4, the value of B4 is generated by code because A4 was changed. The pasted value of B3 is not pasted.

    The only work around I can see to the problem is to provide a toolbar button to run the code. This way pasting into A:B doesn't trigger the change event to default the value for B. Can't think of any other way to do it.
    You know it's time to diet when you push away from the table and the table moves.

  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: Default / Paste Problem (2002)

    Did you run Legare's code?

    If you copy A3:B3, it copies B3 it does not regenerate it from the code using the value in A3.

    Steve

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Default / Paste Problem (2002)

    Steve, I honestly didn't because it look so much like what I had. I'm never going to do that again! Your encouragement made me look a lot closer and I realized there was second intersect in there checking the B cell. I used it and it worked as I needed it to. Thank you for speaking up and for waking me up! <img src=/S/doze.gif border=0 alt=doze width=15 height=15>
    You know it's time to diet when you push away from the table and the table moves.

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Default / Paste Problem (2002)

    Lagare, my humble apologizes. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Steve encouraged me to revisit your code. It looked very similar to mine but with an extra intersect in it checking for the B cell. I used it and it worked very nicely. Your code was great <img src=/S/artist.gif border=0 alt=artist width=34 height=29> and I learned a lot from it. Thank you so much for your help!
    You know it's time to diet when you push away from the table and the table moves.

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

    Re: Default / Paste Problem (2002)

    I'm glad it worked for you.
    Legare Coleman

Posting Permissions

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