Problem with Subs and If's !

[Tachyon]

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

 

[Dave Peterson]

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]

Brilliant - thanks Dave, I'll give that a shot. The 'Event firing'
philosophy is what I'm missing!

 

[RyanH]

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.

 

[Tachyon]

Thanks Ryan - great help. Learning at light speed here

 

[cush]

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.