MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Shifting focus during error handling

Posted by yu-kuan on August 10, 2000 10:30 PM


I'm trying writing an error handling procedure that will stop the program in mid-tracks, allow the user to edit the data in the Excel worksheets, and then resume operations from where it last left off. I know that using error handling procedures such as Goto Error and Resume, Resume Next, I can stop the program, inform the user of the error, then start again. However, I cannot find a way during the time when the program pauses (after posting the error message) to shift the focus back to the worksheets to let the user edit the datas in the cells. I tried working w/ both the msgbox function of AbortRetryCanel and writing my own UserForm, but in both cases,Excel will not allow the user to shift the focus away from the form, thus making it impossible for the user to edit the data, and then click, say "retry", to continue the calculations, or "ignore" to go on to the next data entry.


Sub test()

Dim a As Integer
Dim Ans As Integer

On Error GoTo CheckError 'A1 and A2 are both empty, thus automatically generating an error
a = Range("a1") / Range("a2")

Exit Sub


Ans = MsgBox("please fix input data", vbAbortRetryIgnore)

Select Case Ans
Case 1
Case 2
Resume Next
Case 3
Case Else
End Select

End Sub

Any help will be greatly apprecaited.


Posted by Ivan Moala on August 20, 0100 12:34 AM

You'll need to look @ the Application.InputBox
method for this.
Look @ the online help, if you require help
on this then post.