My InputBox keeps looping. Even when I press cancel/ Escape

LostCause

Board Regular
Joined
Jul 13, 2006
Messages
99
Firstly, thanks for reading.

If I click the button this macro is assigned to, it loops until a value is entered. I would like to be able to press cancel or escape. Any pointers?

Thanks.


Code:
Sub MyMacro()
Dim RCP_Audit_Result6 As String
Dim Updated_Value6 As String
Dim worksheet_current_three As String
Dim yes As String
Dim no As String

Dim temp_count_three
ActiveSheet.Unprotect
temp_count_three = True
yes = "YES"
no = "NO"
worksheet_current_three = "4 RCP Results"
RCP_Audit_Result6 = Sheets(worksheet_current_three).Range("I16")
Updated_Value6 = UCase(InputBox("Please update value", , RCP_Audit_Result6))
'Sheets(worksheet_current_three).Range("G10") = Updated_Value

Do While temp_count_three = True
    If Updated_Value6 = yes Or Updated_Value6 = no Then
        temp_count_three = False
     Sheets(worksheet_current_three).Range("I16") = Updated_Value6
        'Exit Do
    Else
    Updated_Value6 = UCase(InputBox("Updated value must be YES or NO", , RCP_Audit_Result6))
    End If
Loop
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

LostCause

Board Regular
Joined
Jul 13, 2006
Messages
99
Hmm

I tried this:

Code:
Sub MyMacro() 
Dim RCP_Audit_Result6 As String
Dim Updated_Value6 As String
Dim worksheet_current_three As String
Dim yes As String
Dim no As String

Dim temp_count_three
ActiveSheet.Unprotect
temp_count_three = True
yes = "YES"
no = "NO"
worksheet_current_three = "4 RCP Results"
RCP_Audit_Result6 = Sheets(worksheet_current_three).Range("I16")
Updated_Value6 = UCase(InputBox("Please update value", , RCP_Audit_Result6))
'Sheets(worksheet_current_three).Range("G10") = Updated_Value

Do While temp_count_three = True
    If Updated_Value6 = "" Then Exit Sub
    If Updated_Value6 = yes Or Updated_Value6 = no Then
        temp_count_three = False
     Sheets(worksheet_current_three).Range("I16") = Updated_Value6
        'Exit Do
    Else
    Updated_Value6 = UCase(InputBox("Updated value must be YES or NO", , RCP_Audit_Result6))
    End If
Loop
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub

Now it let's me cancel out but sometimes it doesn't accept the correct answer. Where have I gone wrong?

Thanks!
 

LostCause

Board Regular
Joined
Jul 13, 2006
Messages
99
No problem.

Thank you for your help! I think I know where I've gone wrong... let me do some detective work and I'll post an update

Edit: I was being a complete idiot. The code works perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,918
Messages
5,545,025
Members
410,647
Latest member
bernardazar
Top