double macro?

[jammy_owen]

im just wandering if i could click on a button or text box and it will perform one macro, then if i click it again it will perform a different macro?

any help would be very useful.

 

[Mile-O]

Best to use code to achieve that.

 

[jammy_owen]

could you translate the wording as i don't understand what to change it to?

the macro that i want to perform first is called "MCR_MSG4" and when i click again it is called "MCR_MSG5".
also, if its any use the form that im using it on is called "FRM_Membership Information"

 

[Mile-O]

The code for your Command Button (I'll call it cmdMacro)

Private Sub cmdMacro_Click()

   Static intFlag as Integer
   If intFlag = 0 Then
      DoCmd.RunMacro "MCR_MSG4"
   Else
      DoCmd.RunMacro "MCR_MSG5"
   End If
   intFlag = intFlag + 1

End Sub

That code will allow you to run the first macro once and the second one will run every time you click it again.

If you wish to alternate between the macros on every click then this code will be better.

Private Sub cmdMacro_Click()

   Static intFlag as Integer
   If intFlag = 0 Then
      DoCmd.RunMacro "MCR_MSG4"
   Else
      DoCmd.RunMacro "MCR_MSG5"
   End If
   intFlag = IIf(intFlag = 0, 1, 0)

End Sub

 

[jammy_owen]

i used the top one and it works perfectly!
thanks a bunch mile-o-phile!!

 

[jammy_owen]

hello again, it still works but i would like to do the same but with FIVE macros working one after the other(same as above). i tried making the code myself with:

Private Sub Label2_Click()
Static intFlag As Integer
If intFlag = 0 Then
DoCmd.RunMacro "MCR_MSG"
If intFlag = intFlag + 1 Then
DoCmd.RunMacro "MCR_MSG2"
If intFlag = intFlag + 2 Then
DoCmd.RunMacro "MCR_MSG3"
If intFlag = intFlag + 3 Then
DoCmd.RunMacro "MCR_MSG4"
If intFlag = intFlag + 4 Then
DoCmd.RunMacro "MCR_MSG5"
End If
intFlag = intFlag + 5
End Sub

to me, a non-code user, this code looks OK as i have just altered some of the code that you gave me earlier, but when i click it it says: error: Block If without end If. could you help me out on the problem please??

 

[Mile-O]

For each IF statement you start you need a resulting END IF statement unless you use the ELSE IF within your code then you'll only need the one.

Private Sub Label2_Click() 
   Static intFlag As Integer 
   If intFlag = 0 Then 
      DoCmd.RunMacro "MCR_MSG" 
   ElseIf intFlag = intFlag + 1 Then 
      DoCmd.RunMacro "MCR_MSG2" 
   ElseIf intFlag = intFlag + 2 Then 
      DoCmd.RunMacro "MCR_MSG3" 
   ElseIf intFlag = intFlag + 3 Then 
      DoCmd.RunMacro "MCR_MSG4" 
   ElseIf intFlag = intFlag + 4 Then 
      DoCmd.RunMacro "MCR_MSG5" 
   End If 
   intFlag = intFlag + 5 
End Sub

 

[jammy_owen]

ive replaced the old code with the new code that you gave me and the first macro works when i press it once, but if i press it again nothing happens.

 

[FoFa]

Private Sub Label2_Click()
Static intFlag As Integer
If intFlag = 0 Then
DoCmd.RunMacro "MCR_MSG"
ElseIf intFlag = 1 Then
DoCmd.RunMacro "MCR_MSG2"
ElseIf intFlag = 2 Then
DoCmd.RunMacro "MCR_MSG3"
ElseIf intFlag = 3 Then
DoCmd.RunMacro "MCR_MSG4"
ElseIf intFlag = 4 Then
DoCmd.RunMacro "MCR_MSG5"
End If
intFlag = intFlag + 1
End Sub

 

[Mile-O]

Whoops!, never noticed that 5 lurking at the bottom. Thank, FoFa.

Let's make it neater still so we don't get bogged down in IF THEN and ELSE statements by using the SELECT CASE statement.

Private Sub Label2_Click() 
Static intFlag As Integer  
SelectCase intFlag
   Case Is = 0
      DoCmd.RunMacro "MCR_MSG"
   Case Is = 1
      DoCmd.RunMacro "MCR_MSG2"
   Case Is = 2 
      DoCmd.RunMacro "MCR_MSG3" 
   Case Is = 3
      DoCmd.RunMacro "MCR_MSG4" 
   Case Is = 4
      DoCmd.RunMacro "MCR_MSG5"
   Case Else
      ' do nothing although you may wish to insert an action here
   End Select
   intFlag = intFlag + 1 
End Sub

 

[jammy_owen]

Don't worry, there's no more problems!!
I've just put it in and it works for all of the buttons. thanks again!