Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
Hi - Ok, I'm a real newbie so this will be easy for someone :-)
I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
If your code changes something on the worksheet, then that worksheet_change
event is fired again (and again and again...) So tell excel to stop looking for changes: Private Sub Worksheet_Change(ByVal Target As Range) Application.enableevents = false If Target.Value = "GD" Then Selection.Range("B1").Value = "Graeme John Dixon" Else Selection.Range("B1").Value = "I don't know you" End If application.enableevents = true End Sub I'd add a few checks, too: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'if you want to only check in a specific range: If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler: Application.EnableEvents = False If LCase(Target.Value) = LCase("GD") Then Target.Offset(0, 1).Value = "Graeme John Dixon" Else Target.Offset(0, 1).Value = "I don't know you" End If ErrHandler: Application.EnableEvents = True End Sub Tachyon wrote: Hi - Ok, I'm a real newbie so this will be easy for someone :-) I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
Brilliant - thanks Dave, I'll give that a shot. The 'Event firing'
philosophy is what I'm missing! -- Tachyon "Dave Peterson" wrote: If your code changes something on the worksheet, then that worksheet_change event is fired again (and again and again...) So tell excel to stop looking for changes: Private Sub Worksheet_Change(ByVal Target As Range) Application.enableevents = false If Target.Value = "GD" Then Selection.Range("B1").Value = "Graeme John Dixon" Else Selection.Range("B1").Value = "I don't know you" End If application.enableevents = true End Sub I'd add a few checks, too: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub 'if you want to only check in a specific range: If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler: Application.EnableEvents = False If LCase(Target.Value) = LCase("GD") Then Target.Offset(0, 1).Value = "Graeme John Dixon" Else Target.Offset(0, 1).Value = "I don't know you" End If ErrHandler: Application.EnableEvents = True End Sub Tachyon wrote: Hi - Ok, I'm a real newbie so this will be easy for someone :-) I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
The second If...Then Statement is what is causing the macro to continuely
loop to infinite. The default property for ranges is .Value. Plus, you need to clean up your code a bit, like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ErrorHandler If Target = "GD" Then Range("B1") = "Graeme John Dixon" Else Range("B1") = "I don't know you" End If ErrorHandler: Application.EnableEvents = True End Sub for your second if then statement I would recommend putting that in formula bar in Cell B5, like this = IF(B5="Graeme John Dixon","Test", "") Hope this is helpful. If so please give credit. -- Cheers, Ryan "Tachyon" wrote: Hi - Ok, I'm a real newbie so this will be easy for someone :-) I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
Thanks Ryan - great help. Learning at light speed here :-)
-- Tachyon "RyanH" wrote: The second If...Then Statement is what is causing the macro to continuely loop to infinite. The default property for ranges is .Value. Plus, you need to clean up your code a bit, like this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ErrorHandler If Target = "GD" Then Range("B1") = "Graeme John Dixon" Else Range("B1") = "I don't know you" End If ErrorHandler: Application.EnableEvents = True End Sub for your second if then statement I would recommend putting that in formula bar in Cell B5, like this = IF(B5="Graeme John Dixon","Test", "") Hope this is helpful. If so please give credit. -- Cheers, Ryan "Tachyon" wrote: Hi - Ok, I'm a real newbie so this will be easy for someone :-) I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Subs and If's !
In addition to the other responses, here is a detail that should help you: When you use the code line Selection.Range("B1") ................... if you have selected, say, D10:X20, then "B1" is not the cell "B1" but it is the top row and 2nd col WITHIN your SELECTION. ie CELL E10 Likewise Selection.Range("C3") is actually cell G13 - the 3rd row, 3rd col of you SELECTION. "Tachyon" wrote: Hi - Ok, I'm a real newbie so this will be easy for someone :-) I can't figure why this code loops around in strange ways and doesn't just step through doing the If's and Then's etc. I'll say no more at this stage because it's probably embarassingly blindingly obvious to everyone else! Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = "GD" Then Selection.Range("B1").Value = ("Graeme John Dixon") Else Selection.Range("B1").Value = ("I don't know you") End If If Range("B5").Value = ("Graeme John Dixon") Then Range("A1") = ("Test") End If End Sub -- Tachyon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A problem with nested IF's | Excel Worksheet Functions | |||
A problem with nested IF's | Excel Worksheet Functions | |||
Subs | Excel Programming | |||
Newbie: problem referencing cell range between subs in the same mo | Excel Programming | |||
Nested IF's with Rank Problem | Excel Worksheet Functions |