Call macro based on named range value.

[rbkinkead]

Good afternoon,
I have 6 macros in a module that I call based on the cell value on a particular sheet.

Example:
Named Range value will be a number 1-6
Based on this number, I will call macros 1-6

If I run each macro independently, they all work correctly and they work in another workbook I used it in.
Any help will be appreciated!
See below

Private Sub worksheet_change(ByVal target As Range)

If Range("TOTAL_PAGES") = "2" Then
Call pages2
Else
If Range("TOTAL_PAGES") = "3" Then
Call pages3
Else
If Range("TOTAL_PAGES") = "4" Then
Call pages4
Else
If Range("TOTAL_PAGES") = "5" Then
Call pages5
Else
If Range("TOTAL_PAGES") = "6" Then
Call pages6
Else:
Call pages1
End If
End If
End If
End If
End If
End Sub

 

[StephenCrump]

It's not clear what your question is?

The logic to call pages1, pages2 ... or pages6 looks OK. Or you could use a Select Case to avoid the multiple Else/End If.

I wonder ....

1. Whether you really need six different macros? Are they completely different? If they do similar things, it would be more efficient to have the one macro allowing for the six possibilities.

2. Why the code is in a Worksheet_Change Sub? Do you want to call one of these macros every time any cell changes in this worksheet?

3. Whether you're correctly using Worksheet_Change, which should appear capitalised like this: Private Sub Worksheet_Change(ByVal Target As Range)
Is this code in the relevant Sheet module, or did you write the header line for this Sub yourself?

 

[kevin9999]

Is this what you're looking for?

Private Sub worksheet_change(ByVal target As Range)
    On Error Resume Next
    Application.Run "pages" & Range("TOTAL_PAGES").Value
    On Error GoTo 0
End Sub

 

[rbkinkead]

It's not clear what your question is?

The logic to call pages1, pages2 ... or pages6 looks OK. Or you could use a Select Case to avoid the multiple Else/End If.

I wonder ....

1. Whether you really need six different macros? Are they completely different? If they do similar things, it would be more efficient to have the one macro allowing for the six possibilities.

2. Why the code is in a Worksheet_Change Sub? Do you want to call one of these macros every time any cell changes in this worksheet?

3. Whether you're correctly using Worksheet_Change, which should appear capitalised like this: Private Sub Worksheet_Change(ByVal Target As Range)
Is this code in the relevant Sheet module, or did you write the header line for this Sub yourself?

Hi Stephen,
Thank you for your reply.
I have many sheets within this workbook.
"Data" sheet= All behind the scenes stuff no one will ever see, (Hidden)
One cell in the Data sheet (K19) Named Range("IOEPages"), automatically populates with the number of pages required for the equipment list.
The six macros, (pages1,pages2, etc., set the print area on a different sheet, Inventory of Equipment, (IOE), based on the number of pages required.
When the IOEPages value changes, I need to run the appropriate macro to reset the print area in the IOE sheet.

I hope that makes some sense and I'm not just rambling.
Funny thing, I have another workbook where I used this exact routine and it works perfectly. The only difference is there are only 3 macros for the previous one.

I have not used Select Case before and should probably read up on it, it seems to be easier to manage but I will have to learn more about it.

 

[kevin9999]

I'm a bit confused. If the choice of macro is determined by the named range "IOEPages" on the "Data" sheet - where does the named range "TOTAL_PAGES" from your initial post come into play? In any event, if the number of pages changes via a formula, the sub I proposed in post #3 shouldn't work, you'll need a Worksheet_Calculate() event procedure instead.

 

[StephenCrump]

"Data" sheet= All behind the scenes stuff no one will ever see, (Hidden)
One cell in the Data sheet (K19) Named Range("IOEPages"), automatically populates with the number of pages required for the equipment list.

That will be why your code isn't working. If a formula value changes, this won't trigger Worksheet_Change. You'll need the Worksheet_Calculate event instead.

I have not used Select Case before and should probably read up on it, it seems to be easier to manage but I will have to learn more about it.

@kevin9999's approach is better here.

But it sounds like you'd be better off having the one macro handle all possible page numbers?

 

[kevin9999]

But it sounds like you'd be better off having the one macro handle all possible page numbers?

Agreed