Integrate module into an existing worksheet_change event

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows
Hi

I have the following code, very kindly supplied by maabadi in a module
VBA Code:
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
VBA Code:
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows
Help would be much appreciated, thank you!
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,356
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
Dim Lr1 As Long, Lr2 As Long
and add the red stuff where shown
Rich (BB code):
'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
VBA Code:
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
 
Solution

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows
WOW! Thanks a LOT! Nolan, that's great and works perfectly.

Just a small omission - After this
VBA Code:
    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

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I've just noticed your 'red' code refers to my note above in this line
VBA Code:
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

Well-known Member
Joined
Mar 15, 2013
Messages
1,356
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
' 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

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Many thanks - this line errored 1004
VBA Code:
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 31, 2004
Messages
951
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 15, 2013
Messages
1,356
Office Version
  1. 2010
Platform
  1. Windows
I've tried adding "Sheets("Indoor Bike").Select" but that doesn't take me to Col C of the last row.
try
VBA Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,394
Messages
5,769,828
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top