add a new event to an event change macro

[selfteaching]

Hi,

I use this change event macro to move the cursor to column I after data is entered in column C.
It also causes a range to flash when a condition is met (using an if statement.)
in range “difference”

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long
If Not Intersect(Target, Range("c138:c300")) Is Nothing Then
Target.Offset(0, 6).Select
If Range("difference").Value = "MORE Than Last Lease" Then
Application.EnableEvents = False
With Range("difference")
For i = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i

End With
Application.EnableEvents = True
End If
End If
End Sub

I made an if statement to change the value in range ”oilchange”.
How would I modify the macro to also have the range “oilchange” flash when it’s value = Time for an OIL Change


mike

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c138:c300")) Is Nothing Then
With Range("difference")
If .Value = "MORE Than Last Lease" Then
For j = 1 To 10
.Font.ColorIndex = 2+j mod 2
Application.Wait (Now + TimeValue("00:00:01"))
Next
end if
End With
End If
End Sub

[SamT]

This line in your code is not used
Target.Offset(0, 6).Select

To trap multiple change events on a worksheet
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("c138:c300")) Is Nothing Then FlashCertainCells
If Not Intersect(Target, Range("D138:D300")) Is Nothing Then Sub1
If Not Intersect(Target, Range("E138:E300")) Is Nothing Then Sub2
If Not Intersect(Target, Range("F138:F300")) Is Nothing Then Sub3

End Sub


Private Sub FlashCertainCells()

If Range("Difference) = "MORE Than Last Lease" Then
FlashCell Range("Difference)
'If Other Check Then
FlashCell Range("OilChange")
End Sub


Private Sub FlashCell(Rng as Range)

Application.EnableEvents = False
With Rng
For i = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i
End With
Application.EnableEvents = True
End Sub

Think of the entirety of your code as a hierarchy

The Event Sub only checks which Range Changed. It then chooses which subsidiary procedure to run
The Subsidiary Procedures run other checks and decide whether or not to run further Procedures.
Only the last Procedures actually do any work on the sheet.

[selfteaching]

Thank you SamT
I'm glad you showed an explanation.
It's easier to learn that way
The line that you said is not used:

Target.Offset(0, 6).Select
I thought is used to move the cursor 6 cells to the right after an entry is made in any C column cell (the 5 cells in between have IF statements, no data needs to be entered)
That was the first event i made, then I added a flashing code if/when the range "difference" changed.
Now I want the the range "oilchange" to flash if/when it changes.

I'm using range names because they are in boxes that i move around to keep visible
As I enter data I move the box down to keep it in sight.
My next question (when I have time to work on it ) will be: how do I open a sheet and have a range move to an area that will be visible

This event macro was made with the help of this site :yes

mike

[SamT]

I thought is used to move the cursor 6 cells to the right after an entry is made in any C column cell
Yes, but you are not using "Selection" in your code. So why?

how do I open a sheet and have a range move to an area that will be visible


Private Sub Worksheet_Activate()
MovemyRange
End Sub


Private Sub MovemyRange()
'Moves myRange to the third Row from the top of the visible window.
With myRange
.Cut
Windows(1).VisibleRange.Cells(3, myRange.Column).Insert
End With
End Sub

[selfteaching]

Hi

I finally found time to work on this
I wasn't able to get the movemyrange to work. It took a while to realize that I had to name the range to "myrange" :banghead:
Then, I added Selection to .Cut (because I was getting a syntax error) by doing a "record macro" and see how excel did it.
then the next line showed a syntax error so I did the record thing again and changed it a little
this is what I came up (what it does is move the range "box' down one cell and put the cursor back to the first empty cell in A) (the sheet opens with the first 4 rows frozen)

Sub movebox()
Range("box").Select
Application.CutCopyMode = False
Selection.Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Range("A5").Select
x = Range("a" & Rows.Count).End(xlUp).Row
Range("A" & x + 1).Select
ActiveWindow.ScrollRow = ActiveCell.Row - 8
End Sub

I enjoy learning this way. Thank you :yes
I had to change some of the references of the other code and I couldn’t get the flashing to work.
To start over and explain it better
I’m trying to have a change event happen for 4 different changes
1> when a date is entered in A138:A300, I want it to trigger the macro “movebox”
2> when a value is entered in C138:C300, I want the cursor to move right 6 cells
3> when the text in range ”difference” changes due to when an If statement is true, I want it to flash
4> when the text in range “oilchange” changes due to when an if statement is true, I want it to flash, also.
The ranges “difference” and “oilchange” are cells that will change text base on an If statement >> Example:

=IF(K163>0,"MORE Than Last Lease","LESS Than Last Lease ")

What seems to happen is only the first event triggers something. Only the one that is the first event will work. I tried each one as the first one
ALSO , if one of the flashing event is first, it will flash with any of the other event changes.(but only if the IF statement is true).
Like if # 4 is first and "oilchange"="Time for an OIL Change", when I enter a date in A138,or a value in C138, the oilchange range flashes

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("a138:a300")) Is Nothing Then
movebox
If Not Intersect(Target, Range("c138:c300")) Is Nothing Then
Target.Offset(0, 6).Select
‘ If I don’t use the .Select, the line turns red and I get a Syntax error
If Range("difference").Value = "MORE Than Last Lease" Then
flashmore
If Range("oilchange").Value = "Time for an OIL Change" Then
flashoil
End If
End If
End If
End If
End Sub


Sub flashmore()

Dim i As Long
If Range("difference").Value = "MORE Than Last Lease" Then
Application.EnableEvents = False
With Range("difference")
For i = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i
End With

Application.EnableEvents = True
End If
End Sub


Sub flashoil()

Dim i As Long
If Range("oilchange").Value = "Time for an OIL Change" Then
Application.EnableEvents = False
With Range("oilchange")
For i = 1 To 5
.Font.ColorIndex = 2
Application.Wait (Now + TimeValue("00:00:01"))
.Font.ColorIndex = 3
Application.Wait (Now + TimeValue("00:00:01"))
Next i
End With
Application.EnableEvents = True
End If
End Sub

I’m learning macros by adapting what I have, to what I need. I do a lot of pasting and recording
I’m also using this site to start me off if I am doing something new or screwing up

Also, what does the line >> Application.EnableEvents = False do?

Thank you for your patients and understanding

mike

[snb]

You'd better start with:

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

[SamT]

Target.Offset(0, 6).Select is correct because you are doing something to the sheet

Your Code, with indenting
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("a138:a300")) Is Nothing Then
movebox
If Not Intersect(Target, Range("c138:c300")) Is Nothing Then
Target.Offset(0, 6).Select
‘ If I don’t use the .Select, the line turns red and I get a Syntax error
If Range("difference").Value = "MORE Than Last Lease" Then
flashmore
If Range("oilchange").Value = "Time for an OIL Change" Then
flashoil
End If
End If
End If
End If
End Sub
With indenting, you can see that you used nested If...Thens. With nested If,,,Thens, the nested ones will only be tested if the outer one is True. But, it it is True the inner one(s) is(are) False.

Note that in my example I use single line Ifs, IOW, no End Ifs. That way. All Ifs will be tested.
nb: Single line If...Thens have an implied End If at the end of the single line.

Change these lines
If Range("difference").Value = "MORE Than Last Lease" Then
flashmore
If Range("oilchange").Value = "Time for an OIL Change" Then
flashoil
To read like
If Not Intersect(Target, Range("K163") Is Nothing Then
'Is not a single Line, does need an End If
Flashmore
Flashoil
End If
Flashmore and Flashoil are checking the text in Change oil and Difference