如何在excel vba中显示消息框时避免运行时错误

2 回答

• 0

  正如John所说，你必须使用 Exit For . 但是你在错误的地方使用它们 .

  将 control 声明为变量也是一个坏主意 . 是的，我指的是

  Dim TextBox1 As String
  Dim TextBox2 As String
  Dim TextBox3 As String
  

  试试这个

  Sub Sample()
      Dim Wbk1 As Workbook, Wbk2 As Workbook, Wbk3 As Workbook
      Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
      Dim tmp1 As String, tmp2 As String, tmp3 As String
  
      tmp1 = ThisWorkbook.Sheets("Sheet1").TextBox1.Value
  
      If Len(Trim(tmp1)) = 0 Then
          MsgBox "file not chosen"
          Exit Sub
      End If
  
      tmp2 = ThisWorkbook.Sheets("Sheet1").TextBox2.Value
  
      If Len(Trim(tmp2)) = 0 Then
          MsgBox "destination file not selected"
          Exit Sub
      End If
  
      tmp3 = ThisWorkbook.Sheets("Sheet1").TextBox3.Value
  
      If Len(Trim(tmp3)) = 0 Then
          MsgBox "mapping file not selected"
          Exit Sub
      End If
  
      Set Wbk1 = Workbooks.Open(tmp1)
      Set Wbk2 = Workbooks.Open(tmp2)
      Set Wbk3 = Workbooks.Open(tmp3)
  
      Set Sh1 = Wbk1.Sheets("Inventory")
      Set Sh2 = Wbk2.Sheets("Inventory")
      Set Sh3 = Wbk3.Sheets("Sheet1")
  End Sub
  

  完成所有这些操作后，如果仍然收到消息 "file not found or check the spelling and location of the file" ，则表示文本框中提到的路径不正确 . 您实际上可以使用 DIR 来检查路径是否正确 .

  例如

  If Dir(tmp1) = "" Then
       Msgbox "Incorrect Path/File. Please ensure that the Textbox Has correct path"
   Else
       Set Wbk1 = Workbooks.Open(tmp1)
   End If
  

  回复于 2024-05-18T19:50:18+08:00
• 1

  您提供的代码片段对我来说很好，所以我假设您在此例程中有其他代码，在msgbox中单击“确定”后继续运行 . 该代码最有可能是尝试访问文件名=没有导致您收到的错误消息 . 基于这个假设，为什么不只是添加：

  exit sub
  

  填写你的msgbox代码 .

  回复于 2024-05-18T19:50:18+08:00