Thread: Have Macro determine which Macro to run.

Here is the scenario. I have a template that other users fill out and I run a macro based on the data that they populate in the template. I have three destinations for this data depending on how a particular cell is coded (validation list box with three choices). Currently I have to determine the how the cell is coded and run the corresponding macro. I would like to have one overall macro that can look at the cell with the coding and determine which of the three macros to run and then execute that macro. I was originally thinking of something like an If/Else statement, but that will only work if there are two options. Is there something else that I could use that will make this work?

A worksheet change macro would work.

What the

• 3 key words
• names of the 3 macro's
• Cell that the validation list is in

VBA Noob

I am not familiar with the working with that function, so I don’t think that it will work

If you're not familiar then how do you know it won't work

The user takes the template and saves it (File A). I then open up File A and run a macro that transfer information into File B. Now depending again on the cell is coded in File A, it will take one of three macros to transfer this information. I do not need to save either File A or File B with a different name nor do I want to change the sheet names within either workbook.

a proper a example would be more helpful

VBA Noob

I was originally thinking of something like an If/Else statement, but that will only work if there are two options

You can have more than 2 options using If statements

I suggest in a module shhet you type in IF hit F1 to bring up the help file

I also suggest you look at Select Case commands

PS - If you do not understand a suggestion in a reply I recomend you follow up by asking more questions or reading up on the commands etc.
Don't just say you

so I don’t think that it will work

Using comments like that is a good way to have helpful people ignore your requests for help as it appears you will ignore what is suggested to you.

If you do feel something will not work and you have a valid reason beside

I am not familiar with the working with that function

then do reply listing your reasons & concerns as to why you think it will not work


Based on your request Rok's suggestion of a worksheet change macro would work

Alright, here is what I have. It does not run any of the macros though any thoughts?

Sub Jeff()
If "A20" = "Parts Sale" Then
        Call PackingSlipToParts_SaleQue
    ElseIf A20 = "Warranty" Then
        Call PackingSlipToWarrantyQue
    ElseIf A20 = "Trailer Sale" Then
        Call PackingSlipToTrailerSaleQue
    End If
End Sub

Please take a couple of minutes and read ALL theForum Rules then wrap your VBA code (Rule 3)

Here is the functional code.

Sub MasterPackingSlip()

Dim R As String

Cells.Range("A20").Activate
R = ActiveCell.Value
If R = "Parts Sale" Then
        Call PackingSlipToParts_SaleQue
    ElseIf R = "Warranty" Then
        Call PackingSlipToWarrantyQue
    ElseIf R = "Trailer Sale Parts" Then
        Call PackingSlipToTrailerSaleQue
    End If

End Sub

So the answer to my question was..

Originally Posted by mudraker

You can have more than 2 options using If statements

I suggest in a module shhet you type in IF hit F1 to bring up the help file
work

Thank you for those that helped.

Done adding wrap

Try

Option Explicit

Sub MasterPackingSlip()
    Select Case Range("A20").Value
        Case "Parts Sale": Call PackingSlipToParts_SaleQue
        Case "Warranty": Call PackingSlipToWarrantyQue
        Case "Trailer Sale Parts": Call PackingSlipToTrailerSaleQue
        Case Else: Exit Sub
    End Select
End Sub