时间戳

首页 > 解决方案 > 如果用户表单文本框值为空或可以在范围列表中找到字符串，如何退出子

excel - 如果用户表单文本框值为空或可以在范围列表中找到字符串，如何退出子

问题描述

尽管使用了我在网上找到的许多建议和变体，但我一直在努力解决这个问题。如果用户忘记输入值或者可以在字符串范围内找到该值，我想退出子程序，这与用户表单有关。

这是我为该IF子句尝试的许多变体中的最新变体：

Private Sub cmd_nProj_Click()
    Dim wb As Workbook
    Dim ws As Worksheet, ws_h As Worksheet
    Dim i_rc As Long
    Dim r_home As Range, r_ProdName As Range




    Set wb = Application.ThisWorkbook
    Set ws_h = wb.Sheets("Control")

    i_rc = ws_h.Cells(Rows.Count, 1).End(xlUp).Row

    Set r_home = ws_h.Range("A" & i_rc + 1)
    Set r_ProdName = ws_h.Range("N2:N30")
    If Me.tb_NewProjName = "" Or r_ProdName.Find(What:=Me.tb_NewProjName.Value, LookIn:=xlValues) Is Nothing Then
        MsgBox ("Either you have left the projection name blank or the projection name is already being used, please try again!")
        Exit Sub

    End If
end sub

其他变体我会得到一个带有块变量未设置错误的对象，并且在此迭代中，if 子句无法正常工作，即使文本框值出现在该范围内，也会跳过 if 子句。

提前致谢！

标签： excelvba

解决方案

即使文本框值出现在该范围内，也会跳过 if 子句。

这是因为当您在范围内找不到值时，您正在退出子程序。我添加了一个Not来解决这个问题。

Private Sub cmd_nProj_Click()
    Dim wb As Workbook
    Dim ws As Worksheet, ws_h As Worksheet
    Dim i_rc As Long
    Dim r_home As Range, r_ProdName As Range




    Set wb = Application.ThisWorkbook
    Set ws_h = wb.Sheets("Control")

    i_rc = ws_h.Cells(Rows.Count, 1).End(xlUp).Row

    Set r_home = ws_h.Range("A" & i_rc + 1)
    Set r_ProdName = ws_h.Range("N2:N30")
    If Me.tb_NewProjName = "" Or Not (r_ProdName.Find(What:=Me.tb_NewProjName.Value, LookIn:=xlValues) Is Nothing) Then
        MsgBox ("Either you have left the projection name blank or the projection name is already being used, please try again!")
        Exit Sub

    End If
End Sub

推荐阅读