PDA

View Full Version : Solved: If loops



khalid79m
09-26-2008, 05:16 AM
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
09-26-2008, 05:29 AM
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
09-26-2008, 07:56 AM
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
09-26-2008, 08:01 AM
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
09-26-2008, 08:23 AM
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
09-26-2008, 07:00 PM
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
09-27-2008, 03:28 AM
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
10-23-2008, 04:31 AM
thanks it works great