Adding custom error message to excel macro

Question

The macro below opens up an excel workbook and builds a chart on the provided data. I would like to add some error handling so that in the event an excel workbook named differently to the one specified (Employee_source_data) is provided then a message box is displayed e.g Please ensure spreadsheet name provided is "Employee_source_data". Any ideas how to accomplish this? Thanks in advance!

Sub GenerateEmployeeReport()
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Employee_source_data"

    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
    Range("E2:E7").Select
    Range("A1:A7,E1:E7").Select
    Range("E1").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'Sheet2'!$A$1:$A$7,'Sheet2'!$E$1:$E$7")
    ActiveChart.ChartType = xl3DColumnClustered
End Sub

Ok, great it's clear what you want to achieve but you haven't told us what you've tried so far. Nobody will write the code for you, you need to demonstrate what you've tried and what does not work :) — user2140173
– user2140173, Commented Feb 28, 2014 at 12:33
Hello @mehow I've messed around with If statements and msg boxes but did not get very far as I am completely new to excel programming and it is a one off requirement :) — OO_Learner
– OO_Learner, Commented Feb 28, 2014 at 12:54
The process is quite simple. You need a variable to store the path and then use InStr() or StrComp() to verify if one string exists within the other. — user2140173
– user2140173, Commented Feb 28, 2014 at 12:55
If you are hard coding that value, then it should open that workbook. You shouldn't have to test it excel will give an error if that workbook is not found, it won't just open another workbook. — user2140261
– user2140261, Commented Feb 28, 2014 at 13:42
Thanks @user2140261, I just want to provide a friendly message for the user in case wrong workbook is provided instead of a runtime error. — OO_Learner
– OO_Learner, Commented Feb 28, 2014 at 13:55

Jimmy Smith · Accepted Answer · 2014-02-28 13:53:21Z

1

The Dir() function will come in handy.
Try something like

if dir(ThisWorkbook.path & "Employee_source_data*") = "" then
    msgbox "Please ensure spreadsheet name provided is Employee_source_data"
end if

Another alternative is to use the FileDialog control if you're in a situation where it'd be easier to have them select it. In my experience, no one sticks to the naming convention less it is enforced.

answered Feb 28, 2014 at 13:53

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2140261 · Accepted Answer · 2014-02-28 14:22:29Z

Sub GenerateEmployeeReport()
    Dim strWorkbookName As String
    Dim Answer As String
Start:
    strWorkbookName = InputBox("Enter Workbook", "Open Workbook")

    On Error GoTo BadWorkbook
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWorkbookName

    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
    Range("E2:E7").Select
    Range("A1:A7,E1:E7").Select
    Range("E1").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range( _
        "'Sheet2'!$A$1:$A$7,'Sheet2'!$E$1:$E$7")
    ActiveChart.ChartType = xl3DColumnClustered
    Exit Sub

BadWorkbook:
    Answer = MsgBox("Please ensure spreadsheet name provided is Employee_source_data", vbRetryCancel)
    If Answer = 4 Then
        GoTo Start
    Else: Exit Sub
    End If
End Sub

The goto command jumps the code to The line title WrongWorkbook and gives your user the chance to exit or continue with the msgbox rety cancel buttons. Then If they retry it restart the sub with goto start, other wise it exits

Collectives™ on Stack Overflow

Adding custom error message to excel macro

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related