运行宏的宏

Question

我正在尝试编写VBA，以便在满足条件的情况下运行宏。问题是我可以让它运行第一个宏，但之后它就结束了！

每个宏都依赖于各自单元格中的真/假结果。

到目前为止，我已经尝试过了：

Sub RUN_ALL_SET_SHEETS()


    If Range("C28").Value = False Then
                MsgBox "No Team Members Selected?"
                End

    ElseIf Range("C28").Value = True Then


    Dim Response As VbMsgBoxResult
    Response = MsgBox("Are you sure you want to set the sheets for the Team Members selected?", vbQuestion + vbYesNo)
    If Response = vbNo Then Exit Sub

    Else
    Return
    End If

    If Range("C10").Value = True Then
        Call Set_Sheet_Daniel
    End If

    ElseIf Range("C12").Value = True Then
        Call Set_Sheet_Gill
    End If

    ElseIf Range("C14").Value = True Then
        Call Set_Sheet_Hollie
    End If

    ElseIf Range("C16").Value = True Then
        Call Set_Sheet_Jo


    ElseIf Range("C18").Value = True Then
        Call Set_Sheet_Laura_H

    ElseIf Range("C20").Value = True Then
        Call Set_Sheet_Laura_K

    ElseIf Range("C22").Value = True Then
        Call Set_Sheet_Lucy

    ElseIf Range("C24").Value = True Then
        Call Set_Sheet_Mark

    ElseIf Range("C26").Value = True Then
        Call Set_Sheet_Richard

    Else

    End If

    Sheets("Header").Select

    MsgBox "Data Refreshed."


End Sub

感谢您的帮助。

Answer 1

这应该是可行的：

Sub RUN_ALL_SET_SHEETS()


If Range("C28").Value = False Then
            MsgBox "No Team Members Selected?"
            End
ElseIf Range("C28").Value = True Then
Dim Response As VbMsgBoxResult
    Response = MsgBox("Are you sure you want to set the sheets for the Team Members selected?", vbQuestion + vbYesNo)
    ' Changed single line if statement here.
    ' Single line if statements wont go to an else.
    If Response = vbNo Then 
        Exit Sub
    Else
        Return
    End If

    If Range("C10").Value = True Then
        Call Set_Sheet_Daniel
    ElseIf Range("C12").Value = True Then
        Call Set_Sheet_Gill
    ElseIf Range("C14").Value = True Then
        Call Set_Sheet_Hollie
    ElseIf Range("C16").Value = True Then
        Call Set_Sheet_Jo
    ElseIf Range("C18").Value = True Then
        Call Set_Sheet_Laura_H
    ElseIf Range("C20").Value = True Then
        Call Set_Sheet_Laura_K
    ElseIf Range("C22").Value = True Then
        Call Set_Sheet_Lucy
    ElseIf Range("C24").Value = True Then
        Call Set_Sheet_Mark
    ElseIf Range("C26").Value = True Then
        Call Set_Sheet_Richard
    Else

    End If

    Sheets("Header").Select

    MsgBox "Data Refreshed."
End Sub

为了在BruceWaynes注释的基础上构建，这似乎更适合于带有args的子例程。它看起来像这样：

Sub Sheet_By_Name(sName as String)
    ' This is just a demonstration. You would have to put your code
    ' in this block. This also assumes the same operation is needed
    ' for each name.

    ' Checks to ensure a sheet with the supplied name exists
    If Not ThisWorkbook.Sheets(sName) is Nothing Then
        ' Your code would replace this. It is best to avoid activate and
        ' select as is. Again, just for demonstration.
        ThisWorkbook.Sheets(sName).Activate
    Else
        msgbox "A sheet with the name " & sName & " doesn't exist!"
        Exit Sub
    End If
End Sub