On Error

[gheyman]

I use this on Error a lot, but have to admit I am not sure how it actually works.

On Error Resume Next
On Error GoTo 0

I have this code that will error on "With this Workbook - Sheets("Bid Years"..... Which I understand why. I am using Power Query and if certain data isnt available or the user didnt enter some data, the query will not run. Its really not an issue because it comes down to it being a user error more so than coding.

But what I would like to do is if this code does error I want to stop the code and have a msgbox pop up (You do not have the proper data entered for this proposal. Top Lever Assemblies, PBoMs may not have been input into ProPricer. Please enter the correct data or see the applicable Material Estimator.)

How do I do this? Thanks for the Help.

Private Sub CommandButton1_Click()
'Get input from ListBox
ListBoxValue = ListBox1.Text

'Store input in the worksheet
Sheets("Step 1").Range("B11").Value = ListBoxValue

    With ThisWorkbook
         Sheets("Bid Assemblies").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
     
         With ThisWorkbook
         Sheets("Bid Years").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
     
         With ThisWorkbook
         Sheets("PBoM by Task").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
     
         With ThisWorkbook
         Sheets("Costed PBoMs").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
     
              With ThisWorkbook
         Sheets("Consolidated Parts").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
     End With
     
     
     
    Range("Table8[Proposal]").Select
        ActiveCell.FormulaR1C1 = _
            "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C[1],'Proposal List'!C[-2])),"""")"

    Range("Table8[Version]").Select
        ActiveCell.FormulaR1C1 = _
            "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C,'Proposal List'!C[-2])),"""")"


UserForm1.Hide

Sheets("Step 1").Select
    Range("B11").Select
End Sub

Thank You!

 

[Jeffrey Mahoney]

These are my recommended changes.
On error Resume Next will bypass an error and continue with the next line
On Error GOTO SomeWhereElse will jump to the line marked with SomeWhereElse:
On Error GOTO 0 will cease any error checking and fail at the next line with an error

Private Sub CommandButton1_Click()
'Get input from ListBox
ListBoxValue = ListBox1.Text

'Store input in the worksheet
Sheets("Step 1").Range("B11").Value = ListBoxValue

  With ThisWorkbook
    .Sheets("Bid Assemblies").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    On Error GoTo WaitJustAMinute
    .Sheets("Bid Years").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    On Error GoTo 0
      
    
    .Sheets("PBoM by Task").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    .Sheets("Costed PBoMs").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    .Sheets("Consolidated Parts").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
   End With
     
     
     
    Range("Table8[Proposal]").FormulaR1C1 = _
            "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C[1],'Proposal List'!C[-2])),"""")"

    Range("Table8[Version]").FormulaR1C1 = _
            "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C,'Proposal List'!C[-2])),"""")"


UserForm1.Hide

  Sheets("Step 1").Select
  Range("B11").Select
  
Exit Sub
WaitJustAMinute:
MsgBox "You do not have the proper data entered for this proposal. Top Lever Assemblies, PBoMs may not have been input into ProPricer. Please enter the correct data or see the applicable Material Estimator."
End Sub

 

[johnnyL]

Here is an untested shortened version. Hopefully I didn't shorten it up too much and break it.

Private Sub CommandButton1_Click()
'
    Dim ws As Worksheet
'
    Sheets("Step 1").Range("B11").Value = ListBox1.Text                                     ' Get input from ListBox and Store input in the worksheet

    With ThisWorkbook
        On Error GoTo ErrorHandler
'
        For Each ws In Sheets(Array("Bid Assemblies", "Bid Years", "PBoM by Task", "Costed PBoMs", "Consolidated Parts"))
            ws.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
        Next
'
        On Error GoTo 0
     End With
'
    Range("Table8[Proposal]").ActiveCell.FormulaR1C1 = "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C[1],'Proposal List'!C[-2])),"""")"
    Range("Table8[Version]").ActiveCell.FormulaR1C1 = "=IFNA((XLOOKUP([@[Proposal Id]],'Proposal List'!C,'Proposal List'!C[-2])),"""")"
'
    UserForm1.Hide
'
    Sheets("Step 1").Range("B11").Select
    Exit Sub
'
ErrorHandler:
    MsgBox "You do not have the proper data entered for this proposal. Top Lever Assemblies," & vbCr & _
        " PBoMs may not have been input into ProPricer. Please enter the correct data or see the applicable Material Estimator."
End Sub