Is it possible to have a single button trigger one of a range of macros depending on a worksheet variable

[adovelikeboy]

Currently I have a range of 8 macros that are called by a series of worksheet events. The worksheet events are triggered by the user selecting one of 8 variables from a pull-down menu.

The problem with this method is: If the user does not reset their chosen value in the pulldown menu to the null value, then the workbook continues to trigger the worksheet events, which continue to call the macros, and the whole thing becomes a jittering, buggy mess.

Can I get around this problem but still use the pull-down menu but only have it trigger the appropriate menu if I insert a button?

Or should I insert 8 separate buttons, each linked to one macro?

Thanks

 

[Blade Hunter]

Leave your drop down, first thing you do is disable events (application.enableevents = false) read the value of it in to a variable (so you know what they chose) then set the value of it to null, at the end of your code use application.enableevents = true to turn them back on for the next run. problem solved.

Cheers

Dan

 

[austin350s10]

Maybe:

Private Sub Worksheet_Calculate()
If Range("").Value = "" Then    'put the cell you want to check in the first set of ""
    Call Macro1                 'and the dropdown item you are looking for in the second set of ""
Else
    If Range("").Value = "" Then
        Call Macro2
    End If
Else
    If Range("").Value = "" Then
        Call Macro3
    End If
End If
End Sub

 

[adovelikeboy]

Perhaps I need to ask a different, simpler, question.

It occurs to me that my worksheet change event lacks an "End Sub" command after the first routine I'm running. Instead I had all the various commands - reponding to a large number of user controlled variables - listed under one worksheet change event.

What I need to learn is how to have several separate worksheet change events listed in the code for one worksheet, without having them interfere with each other. I don't know the naming protocols to follow, or even it it is possible.

Here's a discription of my current problem:

When the user sets the first variable it selects then executes the first macro.

However, unless the user then sets that first variable to a null value, when the user sets the second variable, the worksheet event selects and executes not only the second macro, but the also the first macro again - because it's still reading the first variable.

 

[Blade Hunter]

Please post your code and we will be able to advise.

Cheers

Dan

 

[adovelikeboy]

Thanks Dan

Here's the worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("g32") Then
Select Case Target.Value
Case 0: Call Deliverables0
Case 1: Call Deliverables1
Case 2: Call Deliverables2
Case 3: Call Deliverables3
Case 4: Call Deliverables4
Case 5: Call Deliverables5
Case 6: Call Deliverables6

End Select
End If

If Range("g60") = "Yes - Choose Existing Vendor" Then
Call ExVen1

End If ...followed by 11 other similar "If" statements before we reach the End Sub command.

The first part of the statement works fine, but once the user starts entering values in the subsequent cells linked to the later worksheet events, it also causes the "Deliverable" macro called by cell g32 to run again, and again...

 

[Blade Hunter]

The first line of the sub should be:

application.enableevents = false

the last line in the sub should be

application.enableevents = true

I believe this will resolve your issue.

Cheers

Dan