CODEWITHSUNDEEP
excelvba
SadMrFrown
SadMrFrown

Reputation: 157

How to exit a sub if the userform textbox value is empty or if the string can be found in a list of ranges

I've been struggling to figure this out, despite using many recommendations and variations that I've found online. I want to exit the sub if the user has forgotten to enter a value or if the value can be found in a range of strings, this is pertaining to a userform.

Here is the most recent of many variations i've tried for the IF clause:

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

Other variations I would get an object with block variable not set error, and in this iteration the if clause is not working correctly in that even when the textbox value appears in that range, the if clause is skipped over.

Thanks in advance!

Upvotes: 1

Views: 852

Answers (1)

NadAlaba
NadAlaba

Reputation: 302

even when the textbox value appears in that range, the if clause is skipped over.

This is because you are exiting the sub when you don't find the value in the range. I've added a Not to fix that.

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

Upvotes: 1

Related Questions