InputBox - Cancel Button + Message box help

[grady121]

Because I’ve no experience with InputBox’s, I’ve been given a code that allows me to show a list of names starting in Column 1 (A) – Row 10, (“A10”) allowing me to enter Data associated with that name, into Column 9. (“I”)

Unfortunately the list of names can be quite long and if possible, I would like to activate the “Cancel” button if the process has an error or not been completed correctly. Then if the “Cancel” button is clicked – I would like to follow it by a MsgBox, asking if the user wants to re-start the InputData routine from the top, or Exit completely.

Also if possible, I would also like to incorporate a confidence MsgBox at the end of the routine, to confirm if the process has been completed correctly, before moving on the next Sub.

e.g.
If the answer is ‘Yes’ – move on to next Sub.
If the answer is ‘No’ – return to the start of the InputData routine.

Ultimately I suppose create Loop until the user clicks ‘Yes’.

I’ve already tried to capture the confidence MsgBox at the end of the first attempt, It appears to work OK on the first occasion allowing me to re-enter all the Data again, but the confirmation MsgBox does not appear after the second InputBox run, allowing it to move on to next sub.

Sub InputData()
    Dim ListRow, ListColumn, NewDataColumn As Integer
    Dim MyNewData As String

    ListRow = 10: ListColumn = 1: NewDataColumn = 9
    While Sheets(1).Cells(ListRow, ListColumn) <> ""
    MyNewData = InputBox("Enter the Details for:- " & vbNewLine & vbNewLine & Sheets(1).Cells(ListRow, ListColumn), "Add Data", Sheets(1).Cells(ListRow, NewDataColumn))  'Get input
        If MyNewData <> "" Then Sheets(1).Cells(ListRow, NewDataColumn) = MyNewData 'If input is not empty, use the input
    ListRow = ListRow + 1
    Wend

End Sub

    Dim iRet As Integer
    
    iRet = MsgBox("Are you happy with all the Data entered?" & vbNewLine & vbNewLine _
    & "Click 'Yes' to continue and move to next step" & vbNewLine & vbNewLine _
    & "Or click 'No' to Re-enter Data and make amendments", vbYesNo, "Check Scores")

    If iRet = vbYes Then ‘Next Sub
    If iRet = vbNo Then AddData ‘ Start Data entry again

I know I’m asking a lot but any help appreciated.

 

[Ruddles]

Try this:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub InputData()[/FONT]
 
[FONT=Fixedsys] Dim ListRow As Integer, ListColumn As Integer, NewDataColumn As Integer[/FONT]
[FONT=Fixedsys] Dim MyNewData As String[/FONT]
[FONT=Fixedsys] Dim iRet As Integer[/FONT]
 
[FONT=Fixedsys] MyNewData = "x"    [COLOR=green]' dummy value to keep loop alive[/COLOR][/FONT]
 
[FONT=Fixedsys] Do Until MyNewData = ""[/FONT]
 
[FONT=Fixedsys]   ListRow = 10: ListColumn = 1: NewDataColumn = 9[/FONT]
[FONT=Fixedsys]   While Sheets(1).Cells(ListRow, ListColumn) <> "" And MyNewData <> ""[/FONT]
[FONT=Fixedsys]     MyNewData = InputBox("Enter the Details for:- " & vbNewLine & vbNewLine & Sheets(1).Cells(ListRow, ListColumn), _[/FONT]
[FONT=Fixedsys]                 "Add Data", Sheets(1).Cells(ListRow, NewDataColumn))               [COLOR=green]  'Get input[/COLOR][/FONT]
[FONT=Fixedsys]     If MyNewData <> "" Then Sheets(1).Cells(ListRow, NewDataColumn) = MyNewData    [COLOR=green]'If input is not empty, use the input[/COLOR][/FONT]
[FONT=Fixedsys]     ListRow = ListRow + 1[/FONT]
[FONT=Fixedsys][COLOR=green]   ' if myNewData is empty we fall out of this loop to the user prompt[/COLOR][/FONT]
[FONT=Fixedsys]   Wend[/FONT]
 
[FONT=Fixedsys]   iRet = MsgBox("Are you happy with all the Data entered?" & vbNewLine & vbNewLine _[/FONT]
[FONT=Fixedsys]        & "Click 'Yes' to continue and move to next step" & vbNewLine & vbNewLine _[/FONT]
[FONT=Fixedsys]        & "Or click 'No' to Re-enter Data and make amendments", vbYesNo + vbQuestion, "Check Scores")[/FONT]
[FONT=Fixedsys]   If iRet = vbNo Then MyNewData = "x"    [COLOR=green]' user wants to go again so keep the loop alive[/COLOR][/FONT]
 
[FONT=Fixedsys] Loop[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

 

[grady121]

Hi Ruddles

Many thanks for your help on this.

Sorry if it seems I’m “moving the Goalposts”.

Firstly, I’m happy for some Data to be left Blank in column 9, as these will be picked up in the following Sub (“DefValue”) that inputs a ‘default value’ for all the blank cells in column 9, using a different text format. I’m doing it this way as the Average for all the Data entered in column 9 is then placed in a separate cell on the worksheet. So I don’t need the confidence MsgBox to appear if no Data entered in the InputBox.

Secondly, after I have finished entering the Data, the confidence MsgBox appears as expected, but if I click ‘Yes’ the InputBox re-appears asking for the Data again, instead of moving on to the “DefValue” Sub).

If I click ‘No’ – expecting to the InputBox to re-appear, the MsgBox closes and exits the Sub.

At the moment, if I click the ‘Cancel’ button the same confidence MsgBox appears.

Thanks again for your help

 

[grady121]

Thanks again for your response.

Although I've abandoned the use of the Cancel button, with some tweaking of your code I've managed to get the main response I needed.


Thanks

 

[Ruddles]

If I click ‘No’ – expecting to the InputBox to re-appear, the MsgBox closes and exits the Sub... if I click ‘Yes’ the InputBox re-appears asking for the Data again, instead of moving on to the “DefValue” Sub).

Odd... when I click 'No', the InputBox reappears. When I click 'Yes,' the subroutine ends.

Are you sure it behaves like that on your machine?

I’m happy for some Data to be left Blank in column 9... I don’t need the confidence MsgBox to appear if no Data entered in the InputBox.

Remove And MyNewData <> "" from the While statement.

Is that it?

 

[Ruddles]

Ah! These posts crossed on the bitstream! Well shout if you need any more tweaks, although you sound as though you've got it cracked now.

 

[grady121]

Hi Ruddles

> Remove And MyNewData <> "" from the While statement.
Thats what I did to stop the confidence Msgbox if no Dta entered.

I had to add a catch before "Loop".
If they answered Yes, it moved into the next Sub and entered a value onto the worksheet.
But it stayed within the Loop

So I added
If Sheets(1).Range("P7").Value <> "" Then MyNewData = "" ' ("P7") is an average of all Data entered

It might not be pretty, but seems to work.

Still don't have use of the Cancel button, but I can live with that.

 

[Ruddles]

Still don't have use of the Cancel button, but I can live with that.

I'm sure that's fixable. If you decide you'd like to give it one more try, post the code you're currently using and let me know what you want the Cancel button to jump to and I'll take a look at it.