CODEWITHSUNDEEP
excelvbaerror-handlinguserformmsgbox
CSAW
CSAW

Reputation: 114

Excel VBA: How to stop program and return to certain step in code, 'bypassing modal limitations of MsgBox'

I have a user interface where the user enters some information. Then they press a button for a report to be generated. The user however cannot have left certain files open while the report is generated. If the user leaves a file open, I want an error message to pop up to inform them of this. And then I want a way for the user to fix the problem (close the file) without having to restart the program as they already entered information.

I have the MsgBox pop up: 

Do While IsFileOpen(wfileName)
    MsgBox "Please close the Weights Machine Data Excel File '" & getFileName(wfileName) & "' to proceed", vbInformation, "Error"

Loop

The problem is that the user cannot do any thing once the error message happens. I know that MsgBox is modal, but is there a way to get around that. If not I want the user to return to the point before they pressed the generateReport Button, so they do not have to retype any of the information they inputted. How do I go about solving this?

Upvotes: 0

Views: 1741

Answers (2)

Capt_Krunch
Capt_Krunch

Reputation: 90

My response is probably more appropriate as a comment instead of an answer - but I don't have enough reputation yet....Anyway

Why not just call the IsFileOpen(wfileName) in the generateReport button click event. If it's true, display your message and then Exit Sub.

Upvotes: 3

David Zemens
David Zemens

Reputation: 53623

MessageBox results can be captured as a long/integer value. So, you can use them to capture user input, and then route your code accordingly, either by calling other procedures or sending to a GoTo statement, etc.

Sub Example()
Dim mb as VbMsgBoxResult
ShowAgain:
mb = MsgBox("Do you want to see another message box?", vbYesNo) 

If mb = vbYes Then 
    GoTo ShowAgain
Else:
    'Do something else
End If



End Sub

In your case, you might do:

Dim mb as vbMsgBoxResult

mb = MsgBox("Please close the Weights Machine Data Excel File '" & getFileName(wfileName) & "' to proceed", vbOkCancel, "Error")

If mb = vbOk Then 
    Workbooks(getFileName(wfileName)).Save
    Workbooks(getFileName(wfileName)).Close
Else:
    'Do something else...

End If

I'm not exactly sure what you mean by this:

I want the user to return to the point before they pressed the generateReport Button, so they do not have to retype any of the information they inputted. How do I go about solving this? But most likely it can't be done easily unless the button (and all of the information that the user has "entered" so far) is on a UserForm or stored in a Class object or some other persistent piece of memory, etc..

Is simply saving/closing the file sufficient? That should allow the procedure to continue running without the error that would happen if the file is already open.

Upvotes: 1

Related Questions