CODEWITHSUNDEEP
vbaexcelexcel-2007
OO_Learner
OO_Learner

Reputation: 73

Adding custom error message to excel macro

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

Upvotes: 0

Views: 12489

Answers (2)

user2140261
user2140261

Reputation: 7993

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

Upvotes: 1

Jimmy Smith
Jimmy Smith

Reputation: 2451

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.

Upvotes: 1

Related Questions