It all depends on how/when/where you are calling this procedure from. Like I said, if you call it from the VBE, you'll be returned there unless the focus is somewhere else first, like the spreadsheet. Assuming that you're not going to be opening the VBE whenever you want to run this code, this seems like an issue which will work itself out when you've placed the action (to call the routine) where you want it (e.g. button, whatever).
Unless you're doing anything else with your code I would remove all 'Exit Sub' lines. Look at your process: 1) unprotect sheet, 2) prompt user, 3) perform actions based on response, 4) protect sheet. If you exit on step 3, you've already done step 1 (unprotect sheet) and would therefore skip step 4 (reprotect sheet). If anything I would use a GoTo line statement to bring them to step 4, thus skipping any other steps you would want to add after step 3. But if this is all of your code it's pointless, as they'll exit right after the response check and protecting the sheet.
As far as the logical process of your code goes, you've only outlined two conditions on a three condition message box result. You are changing a value if a Yes is clicked, based on an input box value (which could be anything, which you're not checking for, as an example, what if a user enters 0?). You are only giving
another message box if No is clicked, but then doing nothing. And you're not even doing anything if Cancel is clicked. As far as I can tell you really only need a Yes/No or Ok/Cancel message box. If this is the case, I would do something like this, which has checks to ensure proper numerical entry...
Code:
Sub UserInput()
Dim iReply As VbMsgBoxResult
Dim Response As Variant
Dim iNum As Variant
ActiveSheet.Unprotect "XXXXX"
iReply = MsgBox(Prompt:="Do you wish change the current base uplift of 20%", _
Buttons:=vbYesNo, Title:="UPDATE UPLIFT %")
If iReply = vbYes Then
' Accept Number from the user
On Error Resume Next
iNum = Application.InputBox("Please Enter Your New Uplift:", "Uplift: Accept Number", , , , , , 1)
On Error GoTo 0
If IsNumeric(iNum) Then
If iNum <> 0 Then
If Int(iNum) = iNum Then
Worksheets("DataCollection").Range("AF3").Value = iNum / 100
Else
If iNum >= 1 And iNum <= -1 Then
Worksheets("DataCollection").Range("AF3").Value = iNum
Else
MsgBox "Please enter a whole number or percent.", vbExclamation, "UPDATE UPLIFT %"
End If
End If
End If
End If
End If
ActiveSheet.Protect "XXXXX", True, True
End Sub
The above code has some error handling as well as value checking. Users are a fickle thing, and it's best to account for all of their nuances. For example someone might enter a 0.2 rather than 20, but both still may only want 20%. The code above checks if they've first entered a number, then if the number is a whole number or not (no check for negative values at this point, but could be added if it was your requisite). Then there is a check if the number is greater than or equal to -1 and less than or equal to 1, in which case we're assuming they're entering a percentage value and we don't need to divide by one hundred. If a user tries entering a decimal value greater than 1 or less than -1 then they receive the message box "Please enter a whole number or percent."
Again, call this from your worksheet (hook to an event, place on a button/shape click, etc.) and the VBE won't be active when it's done. Call it from the VBE and it will return there when done.
HTH
