PDA

View Full Version : [SOLVED] Simple IF with Worksheet Change Event



Anne Troy
03-15-2005, 12:12 AM
I know this cannot be the best:



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
If Target = "payment" Then
Target.IndentLevel = 1
Else
If Target = "credit" Then
Target.IndentLevel = 1
End If
End If
End If
End Sub

How can I make it sweeter?

(I know that y'all are determined I'm gonna learn VBA.)

johnske
03-15-2005, 01:12 AM
Hi Anne,

Probably a better way, but off the top of my head:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
If Target = "payment" Or Target = "credit" Then Target.IndentLevel = 1
End If
End Sub

EDIT: Just tried the above, it indents for anything in column1 try this instead:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Column = 1 Then
If Target = "payment" Or Target = "credit" Then
Target.IndentLevel = 1
Else
Target.IndentLevel = 0
End If
End If
End Sub


Regards,
John

andy_uk
03-15-2005, 02:50 AM
Hey Anne :hi: ; it crashed for me anyway ; too many End Ifs? Lately I always try & stick to a couple of principles:

#1 - Start with


If Selection.Cells.Count > 1 Then Exit Sub
(to ward off crashing)

#2 - cut down on the If ... End If levels by setting all possible parameters ahead. So instead of:


If Target.Column = 1 Then
(some code)
End If


If Target.Column <> 1 Then Exit Sub
(some code)
(no End If)

HTH,
Andy

Anne Troy
03-15-2005, 06:56 AM
Oh, so NOW you show up, huh? Hee hee!

Thanks, guys!