Default / Paste Problem (2002)

Topic

New Reply

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

Reply | Quote

Replies

Why use a change event rather than just having a formula in column B that adds the hour?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Why use a change event rather than just having a formula in column B that adds the hour?

Reply | Quote

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)

=A2+B2/24

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

=IF(A2,A2+B2/24,"")

and copy it down the column

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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)

=A2+B2/24

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

=IF(A2,A2+B2/24,"")

and copy it down the column

Steve

Reply | Quote

You could use code something like this:

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Lagare, my humble apologizes. 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 and I learned a lot from it. Thank you so much for your help!

Reply | Quote

I’m glad it worked for you.

Reply | Quote

I’m glad it worked for you.

Reply | Quote

Lagare, my humble apologizes. 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 and I learned a lot from it. Thank you so much for your help!

Reply | Quote

You could use code something like this:

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

Reply | Quote