Excel VBA restarting same Subroutine within itself based on VbMsgBoxResult

Question

I'm trying to get my Sub to restart based on MsgBoxReults. The code I have doesn't contain any errors, but won't restart based on the users choice (hopefully, having an IF statement within another IF isn't the issue)

Please assist.

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

End Sub

@J VBA, having and IF inside the IF isn't the issue (depends on the logic you are trying to achieve). See my answer below — Shai Rado, Jul 24 '16 at 17:06

score 2 · Answer 1 · answered Jul 24 '16 at 17:04

Try the code below. You need to setup a variable to get the feedback from the 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

Doug Glancy · Answer 2 · 2016-07-25T00:19:17.210

This moves the loop to a calling sub:

Sub EnterWeatherListItems()
Dim MoreWeather As VbMsgBoxResult

MoreWeather = vbYes
Do While MoreWeather = vbYes
    Call FillWeatherList
    'Assigning a Message Box result as a Variable for Yes/No
    'Using IF statement to decide what happens for each condition
    MoreWeather = MsgBox("Thank you for entering your data " & vbNewLine & "Would you like to enter another?", vbYesNo)
Loop
MsgBox "Thank you for you input.", vbInformation
End Sub

Sub FillWeatherList()
Dim Weather As String
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
    ActiveSheet.Range("C1").End(xlDown).Offset(1, 0).Value = ActiveSheet.Range("C1").End(xlDown) + 1
    ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Value = ActiveSheet.Range("A1").End(xlDown) + 1
    ActiveSheet.Range("B1").End(xlDown).Offset(1, 0).Value = Weather
    Columns("A:C").EntireColumn.AutoFit
End If
End Sub

score 0 · Answer 3 · edited Jul 25 '16 at 10:00

From @Shai Rado's answer but without gotos or variants

Option Explicit

Sub ContinueWeatherList()

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

    ' Loop until user says otherwise
    Do Until NoMoreWeather = vbNo
        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
            NoMoreWeather = MsgBox("Thank you for entering your data " & vbNewLine & "Would you like to enter another?", vbYesNo)

        End If

    Loop

End Sub

I think `Weather = InputBox ...` needs to be moved inside the `Do Until` loop — YowE3K, Jul 24 '16 at 22:24

Excel VBA restarting same Subroutine within itself based on VbMsgBoxResult

3 Answers3