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...
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...
Under module: ThisWorkbook
[VBA]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
[/VBA]
Use a worksheet rather than a workbook event. It keeps things simpler
[vba]
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
[/vba]
GTO,
You're missing Target from Instersect, an "End If", and you have quotes after Call which will cause an error.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.Originally Posted by xld
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.Originally Posted by GTO
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
thanks it works great