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

[darrenkaye]

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

[rvasquez]

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!

[darrenkaye]

Hi rvasuez,

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

Darren

[rvasquez]

Can you please tell me the formula in cell H1?

Thanks!

[darrenkaye]

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"))))))

[rvasquez]

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.

Please Login or Register  to view this content.

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

[darrenkaye]

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

[rvasquez]

Okay, then just use

Please Login or Register  to view this content.

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.

[darrenkaye]

Brilliant, works a treat!

I will be adding rep, thanks again.

[rvasquez]

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

Thanks!