How can I make a msgbox when an error appears?

Question

I am trying to import info from my textboxes to specific sheets in another workbook(book1). The macro will find the specific sheet via info typed out in textbox1. (There are pre-made sheets on book1) When they are finished, A MsgBox will appear that says submitted successfully.

Here's my question:

If there are no sheets matching what is written in textbox1, how can i put in an error msgbox that says "User Not Found"?

Private Sub CommandButton1_Click()
Dim Path As String
Dim Filename As String

Path = "C:\Users\OneDrive\Desktop\proj\"
Filename = "Book1" & ".xlsx"
Workbooks.Open Filename:=Path & Filename


With Workbooks(Filename).Worksheets(TextBox1.Text)
`this finds the worksheet in book1`
  .Range("A1").Value = Me.TextBox1.Text
  .Range("B1").Value = Me.TextBox2.Text
  .Range("C3").Value = Me.TextBox3.Text
  .Range("D4").Value = Me.TextBox4.Text
End With

`msgbox starts`
 MsgBox "Submitted Successfully"
ActiveWorkbook.Close _
SaveChanges:=True, _
Filename:="C:\Users\OneDrive\Desktop\proj\Book1.xlsx"
Unload Me

End Sub

I tried using the on error resume next but I really want to have a msgbox that says that it did not find what was put in. i'm not really sure on how to proceed with the if else statements because I dont know where I can put any if else statements. I really hope you can help.

Answer 1

You can do something like this:

Private Sub CommandButton1_Click()
    'use Const for fixed values
    Const Path As String = "C:\Users\OneDrive\Desktop\proj\"
    Const Filename As String = "Book1.xlsx"
    
    Dim wb As Workbook, ws As Worksheet
    
    'get a reference to the opened workbook
    Set wb = Workbooks.Open(Filename:=Path & Filename)
    On Error Resume Next  'ignore error if no sheet matches
    Set ws = wb.Worksheets(TextBox1.Text)
    On Error GoTo 0       'stop ignoring errors
    
    If Not ws Is Nothing Then 'found the sheet?
        ws.Range("A1").Value = Me.TextBox1.Text
        ws.Range("B1").Value = Me.TextBox2.Text
        ws.Range("C3").Value = Me.TextBox3.Text
        ws.Range("D4").Value = Me.TextBox4.Text
        MsgBox "Submitted Successfully"
        wb.Close True  'save
        Unload Me
    Else
        MsgBox "No worksheet found for '" & TextBox1.Text & "'", vbExclamation
        wb.Close False 'no save
        'Unload Me '? or user corrects name on form?
    End If

End Sub