Solved: If loops

[khalid79m]

I need macro help

if a1 = 0 then
run "ABC"

if a1 = 1 then
run "BCD"

if a1 = 2 then
run "EFG"

if a1 = 3 then
run "XXX"

if a1 = 4 then
run "ZZZ"

Can anyone help...

[GTO]

Under module: ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet1" Then
If Not Application.Intersect(Worksheets("Sheet1").Range("A1")) Is Nothing _
And Not Selection.Cells.Count > 1 Then
Select Case Target.Value
Case 0
'Call "ABC"
Case 1
'Call "BCD"
'etc til done
'Case Else
'If needed
End Select
End If
End Sub

[mdmackillop]

Use a worksheet rather than a workbook event. It keeps things simpler

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing _
And Not Selection.Cells.Count > 1 Then
Select Case Target.Value
Case 0
Call ABC
Case 1
Call BCD
End Select
End If
End Sub



GTO,
You're missing Target from Instersect, an "End If", and you have quotes after Call which will cause an error.
Regards
MD

[Bob Phillips]

He was up till 6:00 am this over in Phoenix MD, hardly surprising if he made a few errors.

On your point, I tend more towards the Workbook events these days, as it keeps the code a bit less fragmented. What with multiple modules, classes, forms, DLLs et al, it is hard keep track at times. It is not an absolute, but I do find it happening more.

[mdmackillop]

On your point, I tend more towards the Workbook events these days, as it keeps the code a bit less fragmented. What with multiple modules, classes, forms, DLLs et al, it is hard keep track at times. It is not an absolute, but I do find it happening more.

I wouldn't argue with complex workbooks, but if I only have a few distinct events, and for a "beginner", I'd go with the KISS method.

[GTO]

MD - OOPS and OUCH! Thank you for the corection, I was a wee bit tired but no excuse.

khalid79m - My apologies for the delirium coding, hopefully you didn't check this prior to MD's corrections.

Hope you were able to use MD's :-)

Mark

[mdmackillop]

MD - OOPS and OUCH! Thank you for the corection, I was a wee bit tired but no excuse.

khalid79m - My apologies for the delirium coding, hopefully you didn't check this prior to MD's corrections.

Hope you were able to use MD's :-)

Mark
Happy to help.:beerchug:

[khalid79m]

thanks it works great