User Exit Through Cancel or Close in FOR/DO LOOP

[myactiondesign]

Hi all,

I've got a fully functioning macro that cycles through a range of cells and inserts user input sales data into each cell, one-by-one.

The problem is that you cannot cancel or exit.

Sub FirstSalesInput()

'This sub will create six messages boxes for user input
'The user input will be the first six weeks' sales of a new product
'The data will automatically be input into "Data"




Dim wbCurrent As Workbook
Dim inWeeknum As Integer
Dim rngWeeks As Range
Dim sales As Variant


Set wbCurrent = ActiveWorkbook


intWeeknum = 1


    For Each rngWeeks In Worksheets("Data").Range("D10:D15")
        Do
            sales = InputBox("Please enter unit sales for week " & intWeeknum)
                Debug.Print sales
        'User input must be a number and within predefined assumptions of min and max sales
                    If IsNumeric(sales) And sales <> "" And sales > 1000 And sales < 5000 Then
                    
                        rngWeeks.Value = sales
                        intWeeknum = intWeeknum + 1
        Exit Do
                    Else
                        MsgBox "Please enter a valid sales figure"
[COLOR=#b22222][I]'The problem is [B]here[/B] - if the user needs to exit for whatever reason, they can't, and have to cycle through the
whole process and re-do it all again if there was an error.[/I][/COLOR]
                    End If
        Loop
    Next rngWeeks


'Select innocuous cell
    Sheets("Data").Select
    Range("A1").Select
    
'Return to original sheet
    Sheets("Home").Activate


End Sub

I'm really struggling with this and a tight deadline - the model can work as is, but the user won't be happy with me if they make a mistake!

Any help would be much obliged!

Thanks again all!

!&...

 

[krausr79]

In the input box I'd append " or leave blank to cancel" and then next line, if sales = "" then exit do

 

[MickG]

Perhaps something like this:-

     Else
                        If MsgBox("Do you want to Exit Sub", vbYesNo + vbQuestion, "Accept/Reject") = vbYes Then
                              Exit Sub
                        Else
                              MsgBox "Please enter a valid sales figure"
                       End If
                        
                        
'The problem is here - if the user needs to exit for whatever reason, they can't, and have to cycle through the
'whole process and re-do it all again if there was an error.
                    End If

 

[myactiondesign]

Thank you both!

The first one is so simple and eloquent, but as I took advantage of the second one as I can't trust the user to not enter anything - it has to be idiot proof!

Thanks a lot - I love this site!

Hopefully one day I will be able to give back - I'm just no Excel expert yet, still hovering the amateur/intermediate field right now!

Take care!

 

[MickG]

You're welcome