Excel VBA 基于 VbMsgBoxResult 重新启动其内部相同的子例程

Question

我正在尝试根据 MsgBoxReults 重新启动我的 Sub。我的代码不包含任何错误，但不会根据用户的选择重新启动(希望在另一个 IF 中包含 IF 语句不是问题)

请帮忙。

Sub ContinueWeatherList()

Dim Weather As String
'Assigning a Message Box result as a Variable for Yes/No
Dim MoreWeather As VbMsgBoxResult

Weather = InputBox("Type in the weather for " & Range("C1").End(xlDown) + 1)

If Weather = "" Then
    MsgBox ("No data entered. Your response has not been recorded"), vbExclamation
Else
    Range("C1").End(xlDown).Offset(1, 0).Value = Range("C1").End(xlDown) + 1
    Range("A1").End(xlDown).Offset(1, 0).Value = Range("A1").End(xlDown) + 1
    Range("B1").End(xlDown).Offset(1, 0).Value = Weather
    Columns("A:C").EntireColumn.AutoFit
    MsgBox "Thank you for entering your data " & vbNewLine & "Would you like to enter another?", vbYesNo

    'Using IF statement to decide what happens for each condition
    If MoreWeather = vbYes Then
        ''Call' command won't reinitiate Sub / *NEED TO FIX*
        Call ContinueWeatherList
    Else
        MsgBox "Thank you for you input.", vbInformation
    End If

End If

结束子

Answer 1

尝试下面的代码。您需要设置一个变量来从VBYesNo MsgBox获取反馈。

Option Explicit

Sub ContinueWeatherList()

Dim Weather As String
'Assigning a Message Box result as a Variable for Yes/No    
Dim MoreWeather As Variant

' add label to restart to
ContinueWeatherList_Restart:
Weather = InputBox("Type in the weather for " & Range("C1").End(xlDown) + 1)

If Weather = "" Then
    MsgBox ("No data entered. Your response has not been recorded"), vbExclamation
Else
    Range("C1").End(xlDown).Offset(1, 0).Value = Range("C1").End(xlDown) + 1
    Range("A1").End(xlDown).Offset(1, 0).Value = Range("A1").End(xlDown) + 1
    Range("B1").End(xlDown).Offset(1, 0).Value = Weather
    Columns("A:C").EntireColumn.AutoFit
    MoreWeather = MsgBox("Thank you for entering your data " & vbNewLine & "Would you like to enter another?", vbYesNo)

    'Using IF statement to decide what happens for each condition
    If MoreWeather = vbYes Then
        ' use GOTo command and label to reinitiate the sub
        GoTo ContinueWeatherList_Restart
    Else
        MsgBox "Thank you for you input.", vbInformation
    End If

End If

End Sub