Integrate module into an existing worksheet_change event

[Ironman]

Hi

I have the following code, very kindly supplied by maabadi in a module

Dim Lr1 As Long, Lr2 As Long
Lr1 = Sheets("Training Log").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value
Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"
Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"
Sheets("Indoor Bike").Range("J" & Lr2).Value = "Session "
End If
End Sub

I also have the following code in a worksheet_change event, kindly supplied by NoSparks

Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

If Target.Address(0, 0) = Range("A" & Rows.Count).End(xlUp).Address(0, 0) Then
Application.EnableEvents = False
    Range("C" & Target.Row).Select 'move to this cell to start inputting data

    MsgBox "Enter distance", vbInformation, "Indoor Bike Session Data"
End If

' jump from C to H on that same row
If Target.Address(0, 0) = Range("C" & lr).Address(0, 0) Then
    Range("H" & lr).Select
    MsgBox "Enter Average Watts", vbInformation, "Indoor Bike Session Data"
End If

If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
    Range("J" & lr).Select
Application.EnableEvents = True
End If

I just need the above sets of code to be combined as a worksheet_change event.

The code needs to start in Col C, not A as written above, because A is now copied as part of the first set of code. It then needs to jump from Col C to Col H and then Col J.

Many thanks!

 

[Ironman]

Help would be much appreciated, thank you!

 

[NoSparks]

It doesn't make sense to me to go to your Indoor Bike sheet and start filling data
in the middle of a record that doesn't really exist yet, so my approach would be slightly different.
Seeing that it is a Worksheet_Change of Training Log that puts "Inside bike session" into column I
why not carry on from there for writing to the Indoor Bike sheet

I think this is what I'd do:
change what you show as your second code to this

Dim lr as Long
lr = Range("A" & Rows.Count).End(xlUp).Row

' jump from C to H on that same row
If Target.Address(0, 0) = Range("C" & lr).Address(0, 0) Then
    Range("H" & lr).Select
    MsgBox "Enter Average Watts", vbInformation, "Indoor Bike Session Data"
End If

If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
    Range("J" & lr).Select
End If

and in Training Log Worksheet_Change,
add this to the declarations near the top

Dim Lr1 As Long, Lr2 As Long

and add the red stuff where shown

'monitor column B for OTHER and what's in column I
If target.Column = 2 And target.Value = "OTHER" Then
    Range("A" & target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Application.EnableEvents = False
    Range("I" & target.Row).Value = "Indoor bike session, 60 mins."
    'select cell col F
    Range("F" & target.Row).Select
    Application.EnableEvents = True
    'display message
    MsgBox "Enter heart rate", vbInformation, "Indoor Bike Session Data"
End If

'monitor column F and look for what's in I
If target.Column = 6 And target.Row = Range("A" & Rows.Count).End(xlUp).Row Then
    Lr1 = target.Row
    If UCase(Trim(Left(Sheets("Training Log").Range("I" & Lr1).Value, 19))) = "INDOOR BIKE SESSION" Then
        Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("Indoor Bike").Range("F" & Lr2 & ":G" & Lr2).Value = Sheets("Training Log").Range("F" & Lr1 & ":G" & Lr1).Value
        Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value
        Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & Lr1).Value
        Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"
        Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"
        Sheets("Indoor Bike").Range("J" & Lr2).Value = "Session "
    End If
End If

                                         'Florante Kho

then add this event to the Indoor Bike sheet

Private Sub Worksheet_Activate()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    If Range("C" & lr) = "" Then
        Range("C" & lr).Select 'move to this cell to start inputting data
        MsgBox "Enter distance", vbInformation, "Indoor Bike Session Data"
    End If
End Sub

Your recent posts not withstanding,
Good Luck with this,
NoSparks

 

[Ironman]

WOW! Thanks a LOT! Nolan, that's great and works perfectly.

Just a small omission - After this

    MsgBox "Enter heart rate", vbInformation, "Indoor Bike Session Data"
End If

I need to move to Col H with a msgbox "Enter Session Rating" - I then enter the rating, and then that gets added to the copied code in red (copied to Col I).

I guess that's doable?

Thanks ever so much again Nolan!

 

[Ironman]

I've just noticed your 'red' code refers to my note above in this line

Sheets("Indoor Bike").Range("I" & Lr2).Value = Sheets("Training Log").Range("H" & Lr1).Value

There's just no reference to it (Col H in Training Log) in the first half of that code.

 

[NoSparks]

OK...
instead of monitoring column F and looking for what's in I
it needs to be monitoring column H and looking for what's in I
so just change the comment to reflect that and change the 6 to 8 in this line

If target.Column = 6 And target.Row = Range("A" & Rows.Count).End(xlUp).Row Then

For the message thing, you already have this, just add the message

' jump from F to H on the same row
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then
    Range("H" & lr).Select
    MsgBox "Enter Session Rating"    '<<<<<<<<<<<<<<<
End If

Hope that helps
NoSparks

 

[Ironman]

Many thanks - this line errored 1004

If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then

 

[Ironman]

Sorry Nolan, ignore my last msg, I was using it in the test workbook. When I used it in the live workbook it all worked perfectly.

So THANK YOU!!!!

 

[Ironman]

Hi again

Your code's still working great, but I'd really like to polish the sequence if possible. You suggested "why not carry on from there for writing to the Indoor Bike sheet", which you've done and it's brilliant, but to do that I need to manually select the Indoor Bike tab first, when I'm then prompted for the rest of the information to be input.

Is there something I can add to the end of the Training Log code to move to Col C of the last row in Indoor Bike sheet to continue inputting i.e. so I don't have to select the Indoor Bike tab first?

I've tried adding "Sheets("Indoor Bike").Select" but that doesn't take me to Col C of the last row.

Thanks a lot once again!

 

[NoSparks]

I've tried adding "Sheets("Indoor Bike").Select" but that doesn't take me to Col C of the last row.

try

With Sheets("Indoor Bike")
    .Select
    .Range("C" & .Rows.Count).End(xlUp).Select
    '.Range("C" & .Rows.Count).Enc(xlUp).Offset(1).Select
End With

I'm not really sure which row you want to be in so you'll need to decide which line to delete.

Hope that helps.