CODEWITHSUNDEEP
vbaexcel
Jason Nichols
Jason Nichols

Reputation: 3760

Why does Excel VBA prompt me for a Macro name when I press Run Sub

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RR As Range
    Dim TestArea As Range
    Dim foremenList As Range
    Dim workerList As Range
    Dim workers As Range
    Dim Foremen As Range
    Dim i As Integer
    Dim R As Range
    Dim EmplList() As Variant




    Set TestArea = Sheet90.Range("b4:q8", "b15:q19", "b26:q30")
    Set foremenList = Sheet90.Range("V24:V30")
    Set RR = Sheet90.Range("AA25:AA46")
    i = 0





    For Each R In RR.Cells
       If Len(R.Value) > 0 Then
           EmplList(i) = R.Value
           i = i + 1
        End If
    Next R

    Dim ValidStr As String
    Set ValidStr = Join(EmplList, ",")


    With Sheet90.Range("b26").Validation
        .Delete
        .Add xlValidateList, xlValidAlertStop, _
            xlBetween, "1,2,3"
    End With
    Sheet90.Range("b40").Value = "Test"
End Sub

But when I press run to test it, it prompts me for a macro name.

Additionally, it does not trigger on Worksheet_Changeany more.

Is this an error (i.e. I forgot a semicolon or something) that consistently triggers Excel VBA to behave like this? If so, what should I look for in the future?

Upvotes: 1

Views: 16025

Answers (3)

Jason Nichols
Jason Nichols

Reputation: 3760

The problem stems from two lines:

Set ValidStr = Join(EmplList, ",")

was not a valid use of the Set keyword (It's a string and not an object), and 

Set TestArea = Sheet90.Range("b4:q8", "b15:q19", "b26:q30")

apparently has too many arguments.

According to Microsoft, it should be a single string argument like: 

Set TestArea = Sheet90.Range("b4:q8, b15:q19, b26:q30")

Commenting both of these out made the code run fine both with the run sub button, and on the event.

The "Name Macro" dialog is some kind of error indicator, but I still don't know what it means, other than Code Borked

Upvotes: 1

techturtle
techturtle

Reputation: 2587

The reason you can't run this one with the Run Sub button is because it requires a parameter. If you want to run this standalone, one possibility is to run it in the Immediate Window so you can manually pass in the parameter. Since this one is expecting a more complex data type (range) you may want to create a small sub to call it so that you can properly create your range and pass that in. Then you can use the Run Sub on this sub which will call your other one.

As far is it not triggering on Worksheet_Change, I am not able to tell what is causing it just from what you posted. However, you do need to make sure that it is located on the code page for the worksheet you are trying to run it from. If you need the same one to run from multiple sheets, you should put it into a module and call it from each sheet's Worksheet_Change method.

Upvotes: 3

tigeravatar
tigeravatar

Reputation: 26650

You can't press F5 or the run button to run triggered code. You would have to make a change in the sheet where this code is located in order for the code to run. Also, if this code is not located in Sheet90, then you won't see anything happen because this code only makes changes to Sheet90. Lastly, to make sure events are enabled, you can run this bit of code:

Sub ReEnable_Events()
    Application.EnableEvents = True
End Sub

Note that you will still have to enable macros.

Upvotes: 1

Related Questions