Macro: Calling other macros dependant of the result of a specific cell

Hi All,

I wonder if you could help me?

I am trying to write a macro which does the following:

Calls 4 other macros dependant of the result of cell $H$1

For example,

If cell $H$1 is "LEG1", then call legtwoodd
If cell $H$1 is "LEG2", then call legthreeodd
If cell $H$1 is "LEG3", then call legfourodd
If cell $H$1 is "LEG4", then call legfiveodd

I would be appreciative of any input on how to help with the above.

Thanks in advance
Darren

Hello there,

Is the cell H1 a drop down where the user selects a value, or a result of a formula, or a manually entry cell?

Thanks!

Hi rvasuez,

H1 is the result of a formula, hope this helps.

Darren

Can you please tell me the formula in cell H1?

Thanks!

Wouldnt have thought it was relevant, but I'm only new to VBA

=IF(M6+Z6=0,"START",IF(M6+Z6=1,"LEG1",IF(M6+Z6=2,"LEG2",IF(M6+Z6=3,"LEG3",IF(M6+Z6=4,"LEG4",IF(M6+Z6=5,"LEG5"))))))

Okay let's try this, select the worksheet that contains that cell E1 that changes. Right click on the worksheet tab and select the View Code option. In the space provided copy and paste the below code. Anything that appears in green is a comment meant to help you understand.

Private Sub Worksheet_Change(ByVal Target As Range) 'on the worksheet change

'if the cell that was changes is M6 or Z6 then
If Target.Address = "$M$6" Or Target.Address = "$Z$6" Then
    
    Select Case Range("H1").Value   'if the value in cell H1 is equal to...
        Case "LEG1" 'LEG1 then...
            legtwoodd   'run legtwood macro
        Case "LEG2" 'LEG2 then...
            legthreeodd 'legthreeodd macro
        Case "LEG3" 'LEG3 then...
            legfourodd  'run legfourodd macro
        Case "LEG4" 'LEG4 then..
            legfiveodd  'run legfiveoddmacro
    End Select  'end select statements

End If  'end if that cell's changed
End Sub

Close out of Visual Basic and then change the value in either M6 or Z6 to watch it work.

Hi thanks, but I dont believe this would work,

The calling of the other macro would also need to be a macro too, run when the user clicks the 'Next Leg' button.

I am attaching my working file, it may be clearer once viewed. Please view the GAME 2 sheet.

Thanks for your help
Darren

Okay, then just use

Select Case Range("H1").Value   'if the value in cell H1 is equal to...
        Case "LEG1" 'LEG1 then...
            legtwoodd   'run legtwood macro
        Case "LEG2" 'LEG2 then...
            legthreeodd 'legthreeodd macro
        Case "LEG3" 'LEG3 then...
            legfourodd  'run legfourodd macro
        Case "LEG4" 'LEG4 then..
            legfiveodd  'run legfiveoddmacro
    End Select  'end select statements

1. Press Alt+F8 to bring up the macro window
2. Clear the macro name field and then type NextLegOpt
3. Select the Create option
4. In betweent the Sub NextLegOpt() and End Sub copy and paste the above code
5. Close out of Visual Basic
6. In Design Mode right click on the Next Leg button and select the assign macro option
7. Select the NextLegOpt macro.
8. Deselect Design Mode and then attempt to run the macro.

Brilliant, works a treat!

I will be adding rep, thanks again.

No problem, thanks in advance for the star tap and please don't forget to mark this thread solved!

Thanks!