Check for UserForm response in Do Until loop

[grady121]

I have a working InputBox code that uses a 'Do Until' loop to fill in new Data against each name listed in Column B.

What I'm trying to do now is stop the user from simply keep hitting the 'Enter' key to move to next input. And subsequent next Sub.

I have it working with a MessageBox option showing after the final entry has been made, just to check if all OK.

    MyNewData = "x"    ' Dummy value to keep loop alive

Do Until MyNewData = ""

    ListRow = 10: ListColumn = 2: NewDataColumn = 10: NewDataColumn2 = 37
    While ActiveSheet.Cells(ListRow, ListColumn) <> ""
        MyNewData = InputBox("Enter the Details for:- " & vbNewLine & vbNewLine & ActiveSheet.Cells(ListRow, ListColumn), _
                             "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    'Get input
        If MyNewData <> "" Then 
            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  'If input is not empty, use the input
            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData
        Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""
        End If
        ListRow = ListRow + 1
    Wend

   iRet = MsgBox("Are you happy with the Data entered?" & vbNewLine & vbNewLine _
        & "Click 'Yes' to Move on." & vbNewLine _
        & "Or" & vbNewLine _
        & "Click 'No' to Re-enter the Data", vbYesNo + vbQuestion, "Check Scores")
   If iRet = vbYes Then MyNewData = "" ' User wants to move on
   If iRet = vbNo Then MyNewData = "x"    ' User wants to go again so keep the loop alive

Loop

    'Move to Next Sub

What I'm now trying to do is introduce a Modeless Userform that needs to be controlled by a click, instead of a static MsgBox, as this will enable them to access the worksheet and check/scroll through the long list of Data, before moving on.

But I don't know what code lines I need to enter behind the 2 Userforms CommandButtons to either re-start the loop or how to get out of it and move onto the next sub.

At the moment I just have UserForm7.Show as a replacement for the "iRet = MsgBox" lines.
The codes I've put on the two Userform buttons at the moment are as follows:

Private Sub CommandButton1_Click() ' Move On
    MyNewData = ""
    Unload Me
End Sub

Private Sub CommandButton2_Click() ' Go Back
    MyNewData = "x"
    Unload Me
End Sub

But this doesn't work.

Any help appreciated.

 

[AlphaFrog]

<font face=Courier New>    MyNewData = "x"    <SPAN style="color:#007F00">' Dummy value to keep loop alive</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> MyNewData = ""<br><br>    ListRow = 10: ListColumn = 2: NewDataColumn = 10: NewDataColumn2 = 37<br>    <SPAN style="color:#00007F">While</SPAN> ActiveSheet.Cells(ListRow, ListColumn) <> ""<br>        MyNewData = InputBox("Enter the Details for:- " & vbNewLine & vbNewLine & ActiveSheet.Cells(ListRow, ListColumn), _<br>                             "Add Scores", ActiveSheet.Cells(ListRow, NewDataColumn))    <SPAN style="color:#007F00">'Get input</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> MyNewData <> "" <SPAN style="color:#00007F">Then</SPAN><br>            ActiveSheet.Cells(ListRow, NewDataColumn) = MyNewData  <SPAN style="color:#007F00">'If input is not empty, use the input</SPAN><br>            ActiveSheet.Cells(ListRow, NewDataColumn2) = MyNewData<br>        Else: ActiveSheet.Cells(ListRow, NewDataColumn) = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        ListRow = ListRow + 1<br>    <SPAN style="color:#00007F">Wend</SPAN><br>    <br>    UserForm7.Show<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> UserForm7.Visible = <SPAN style="color:#00007F">False</SPAN><br>        DoEvents<br>        Sleep 100   <SPAN style="color:#007F00">'pause exicution for 100 milliseconds</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br><SPAN style="color:#007F00">'   iRet = MsgBox("Are you happy with the Data entered?" & vbNewLine & vbNewLine _<br>'        & "Click 'Yes' to Move on." & vbNewLine _<br>'        & "Or" & vbNewLine _<br>'        & "Click 'No' to Re-enter the Data", vbYesNo + vbQuestion, "Check Scores")</SPAN><br><SPAN style="color:#007F00">'   If iRet = vbYes Then MyNewData = "" ' User wants to move on</SPAN><br><SPAN style="color:#007F00">'   If iRet = vbNo Then MyNewData = "x"    ' User wants to go again so keep the loop alive</SPAN><br><br><SPAN style="color:#00007F">Loop</SPAN><br><br>    <SPAN style="color:#007F00">'Move to Next Sub</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

UserForm7 module
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click() <SPAN style="color:#007F00">' Move On</SPAN><br>    MyNewData = ""<br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click() <SPAN style="color:#007F00">' Go Back</SPAN><br>    MyNewData = "x"<br>    Unload Me<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Standard Module declarations
<font face=Courier New><SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Sleep <SPAN style="color:#00007F">Lib</SPAN> "kernel32" (<SPAN style="color:#00007F">ByVal</SPAN> dwMilliseconds <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)<br><SPAN style="color:#00007F">Public</SPAN> MyNewData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN></FONT>

 

[grady121]

Hi AlphaFrog

It appered at first look you had come up with the perfect solution for me.
I copied all the codes and declarations as you suggested and it seemed to work well for a while.

Unfortunately it doesn't seem stable, with different results being seen after clicking on the UserForm7 - CommandButton1 ' Move On

If I copy & Paste the amended code into the Sub, it works for a while. But if I then save the file and re-open it, it doesn't seem to recognise the Move On code and simply remains in the Loop?
i.e. It keeps asking for the Data to be re-entered, instead of moving onto the next Sub.

Any ideas?

Thanks for your time & effort so far.

 

[AlphaFrog]

Do you really need to pause execution in the middle of the loop? I would suggest you could configure the code to run only once and call UserForm7 at the end. Then the buttons on UserForm7 can call to run another loop or quit.

 

[grady121]

Thanks AlphaFrog

I agree - a lot simpler option.

Its probably why they say "the simple things that work the best" !

Thanks again.