Creating macros with if stmts

L

Lisa12

I am new to macros and I need to create a macro with an if statement. What
is the correct format, including spacing to make this work? I know the
wordin is If and else, but I can't seem to get the right format. Lets say if
A1=1 I want the first macro to work, if A1=2 I want the 2nd macro to work,
and if A1=3 the third.

Sub

Macro

End Sub

Sub

Macro

End Sub

Sub

Macro

End Sub

Any help would be appreciated, like I said I just can't quite make it work.

Thank you,

Lisa12
 
M

Mike H

Hi,

There are many ways to do this. Here's one. You would of course change the
msgbox in this example to your code.

Sub macro()
Select Case Range("A1").Value
Case Is = 1
macro1
Case Is = 2
macro2
Case Is = 3
macro3
Case else
'Do soemthing else
End Select
End Sub

Sub macro1()
MsgBox 1
End Sub

Sub macro2()
MsgBox 2
End Sub

Sub macro3()
MsgBox 3
End Sub

Mike
 
D

Don Guillett

You could/should make it one macro but
Sub selectmacro()
Select Case Range("a1").Value
Case 1: macro1
Case 2: macro2
Case 3: macro2
Case Else
End Select
End Sub

Sub macro1()
MsgBox "1"
End Sub
Sub macro2()
MsgBox "2"
End Sub
Sub macro3()
MsgBox "3"
End Sub
 
N

Normek

Lisa12,

If you want to get the macros to run just by changing the value in the cell
then you will probably have to use a slightly more complex approach, than the
previous posts.

Open the Visual basic Editor and double-click on Sheet1 in the Project
Explorer pane. This will open a code page associated with sheet1.
Click the left hand side dropdown box at the top and select 'Worksheet'.
Then click the right hand dropdown box and select 'Change'

Then use something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Application.Range("A1") Then
If Range("a1").Value = 1 Then 'Alternatively it can all go on one
line as in the second case
macro1
End If
If Range("a1").Value = 2 Then macro2
If Range("a1").Value = 3 Then macro3
End If
End Sub

Sub macro1()
MsgBox "Macro1"
End Sub

Sub macro2()
MsgBox "Macro2"
End Sub

Sub macro3()
MsgBox "Macro3"
End Sub
 
Top