If Textbox.Value, And If TextBox.Value Is not, Else

[Russmeister57]

Hi all,

I have trolled through all of the threads and cannot find the answer, if there is one to fix my code.
I have a sheet "TM Ownership", and on this sheet is a database of colleague details.
I have built a Userform to search for a name within this sheet and return all the data i need.
What i have currently is, if textbox1 is blank - tell me via msgbox else supply me with the data i have requested.
Currently, if i enter a name that isnt in the database, it still tries to look in the database and returns a runtime error 1004.
I cant for the life of me work out how to make it look first for the name, if it isnt in the database, then tell me via msgbox, but if it is, then return the data i have requested, but with keeping in the 'if textbox1.value is "" then' arguement.
I hope this makes sense, and any help is appreciated.

This is the code i have:

Private Sub Search_CDetails_Click()
'Search for Colleague Details
Dim TargetRow As Integer
Dim test1 As Range
Dim c As Range

Set test1 = Sheet6.Range("TM_Ownership_Start")

For Each c In test1

    Next c

        If TextBox1.Value = "" Then
        MsgBox "    No Data Has Been Selected!"
   
            Else
   
            TargetRow = Application.WorksheetFunction.Match(TextBox1, Sheets("TM Ownership").Range("Full_Name"), 0)
            Sheets("data").Range("G5").Value = TargetRow
   
            Colleague_Details.TextBox2 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 1).Value
            Colleague_Details.TextBox4 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 2).Value
            Colleague_Details.ComboBox3 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 3).Value
            Colleague_Details.ComboBox1 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 4).Value
            Colleague_Details.ComboBox4 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 5).Value
            Colleague_Details.TextBox3 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 6).Value
            Colleague_Details.TextBox5 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 8).Value
            Colleague_Details.TextBox6 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 9).Value
            Colleague_Details.TextBox7 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 10).Value
            Colleague_Details.TextBox8 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 11).Value
            Colleague_Details.TextBox9 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 12).Value
            Colleague_Details.TextBox10 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 13).Value
            Colleague_Details.CheckBox1 = Sheet6.Range("TM_Ownership_Start").Offset(TargetRow, 34).Value
       
            End If

Exit Sub

End Sub

 

[HaHoBe]

Hi Russmeister57,

maybe

Private Sub Search_CDetails_Click()
'Search for Colleague Details
' https://www.mrexcel.com/board/threads/if-textbox-value-and-if-textbox-value-is-not-else.1230189/
Dim varRet As Variant

If TextBox1.Value = "" Then
  MsgBox "No Data Has Been Selected!", vbExclamation, "Nothing to do here"
Else
  varRet = Application.WorksheetFunction.Match(TextBox1, Sheets("TM Ownership").Range("Full_Name"), 0)
  If Not IsError(varRet) Then
    Sheets("data").Range("G5").Value = varRet
    With Sheet6.Range("TM_Ownership_Start")
      Colleague_Details.TextBox2 = .Offset(varRet, 1).Value
      Colleague_Details.TextBox4 = .Offset(varRet, 2).Value
      Colleague_Details.ComboBox3 = .Offset(varRet, 3).Value
      Colleague_Details.ComboBox1 = .Offset(varRet, 4).Value
      Colleague_Details.ComboBox4 = .Offset(varRet, 5).Value
      Colleague_Details.TextBox3 = .Offset(varRet, 6).Value
      Colleague_Details.TextBox5 = .Offset(varRet, 8).Value
      Colleague_Details.TextBox6 = .Offset(varRet, 9).Value
      Colleague_Details.TextBox7 = .Offset(varRet, 10).Value
      Colleague_Details.TextBox8 = .Offset(varRet, 11).Value
      Colleague_Details.TextBox9 = .Offset(varRet, 12).Value
      Colleague_Details.TextBox10 = .Offset(varRet, 13).Value
      Colleague_Details.CheckBox1 = .Offset(varRet, 34).Value
    End With
  Else
    MsgBox "No Data found for: " & TextBox1.Value & "!", vbInformation, "No data available"
  End If
End If

End Sub

Problem with this way may be that you would need to have an exact match. Maybe think about an extra Textbox, a CommandButton and a Listbox to do a search with wildcards and list all results found in the listbox from where data could be transferred by clicking on it.

Samples (taken from an old UserForm, not all components would be needed - it's more what might be done) look like

Private Sub btnFindAll_Click()
  Dim strErsteFundstelle As String
  Dim strSuchbegriff As String    
  Dim rngSuchbereich As Range     
  Dim lngZeile As Long
  Dim lngSpalte As Long
  Dim lngZähler As Long
  Dim lngCounter As Long
  Dim lngArray As Long
  Dim lngFill As Long
  Dim myArray()
  
  ReDim myArray(0 To 0, 0 To 9)
  lngZeile = 1
  
  With Me
    .lblAnzahl.Caption = vbNullString
    With .lstAnzeige
      .Enabled = True
      .List() = myArray
    End With
    For lngZähler = 3 To 9
      With .Controls("TextBox" & Format(lngZähler, "00"))
        .BackColor = vbWhite
        .Value = vbNullString
      End With
    Next lngZähler
    If Trim(.TextBox01.Value) <> "" Then
      strSuchbegriff = "*" & Trim(.TextBox01.Value) & "*"
      .TextBox02.Value = ""
      lngSpalte = 1
    ElseIf Trim(.TextBox02.Value) <> "" Then
      strSuchbegriff = "*" & Trim(.TextBox02.Value) & "*"
      .TextBox01.Value = ""
      lngSpalte = 2
    Else
      MsgBox "Kein Suchbegriff bei Kurz- oder Firmenname angegeben!", vbExclamation, "Abbruch"
      Exit Sub
    End If
  End With
  
  With Sheet1
    Set rngSuchbereich = .Range(.Cells(2, lngSpalte).Address, .Cells(.Rows.Count, lngSpalte).End(xlUp).Address)
  End With
  
  lngArray = WorksheetFunction.CountIf(rngSuchbereich, strSuchbegriff)
  If strSuchbegriff = "***" Then lngArray = lngArray - 1
  ReDim myArray(0 To lngArray, 0 To 9)
  
  With rngSuchbereich
    Set m_rng_Gefunden = .Find(strSuchbegriff, After:=rngSuchbereich.Cells(rngSuchbereich.Cells.Count), LookIn:=xlValues)
    If Not m_rng_Gefunden Is Nothing Then    'found it
      lngFill = 0
      'load the headings
      With Me.lstAnzeige
        For lngCounter = 0 To 10
          Select Case lngCounter
            Case 6
            Case Else
              myArray(0, lngFill) = CStr(Sheet1.Cells(1, lngCounter + 1).Value)
              lngFill = lngFill + 1
          End Select
        Next lngCounter
      End With
      
      strErsteFundstelle = m_rng_Gefunden.Address
      
      Do
        'Load details for Listbox into Array
        lngFill = 0
        For lngCounter = 0 To 10
          Select Case lngCounter
            Case 6
            Case Else
              myArray(lngZeile, lngFill) = CStr(Sheet1.Cells(m_rng_Gefunden.Row, lngCounter + 1).Value)
              lngFill = lngFill + 1
          End Select
          
        Next lngCounter
        lngZeile = lngZeile + 1
        Set m_rng_Gefunden = .FindNext(m_rng_Gefunden)
      Loop While Not m_rng_Gefunden Is Nothing And m_rng_Gefunden.Address <> strErsteFundstelle
    Else
      MsgBox "Der Suchbegriff wurde nicht gefunden!" & vbCrLf & "Überprüfen Sie bitte die Schreibweise.", vbInformation, "Hinweis"
    End If
  End With
  
  Me.lblAnzahl.Caption = lngZeile - 1 & " Treffer:"
  'Load data into LISTBOX
  Me.lstAnzeige.List() = myArray
  
  With Me
    .TextBox08.Text = "ohne"
    .TextBox09.Text = "ohne"
    .btnFindAll.Enabled = False
    .btnAdd.Enabled = False
    .btnClone.Enabled = False
  End With
  
  Set m_rng_Gefunden = Nothing
  Set rngSuchbereich = Nothing
End Sub

Private Sub lstAnzeige_Click()
  Dim intListIndex As Integer
  Dim rng As Range
  Dim rngSuchbereich As Range
  Dim lngCmb As Long
  Dim lngCounter As Long
  
  Const cstrSCHLUESSEL As String = "K"
  
  If Me.lstAnzeige.ListIndex = -1 Then    'not selected
  ElseIf Me.lstAnzeige.ListIndex = 0 Then
    With Me
      m_bln_PRUEFEN = True
      .frmAnsprechpartner.Visible = False
      For lngCounter = 2 To 9
        With .Controls("Textbox" & Format(lngCounter, "00"))
          .Value = vbNullString
        End With
      Next lngCounter
      m_bln_PRUEFEN = False
      .cmbAngabe.Value = "Buchhaltung"
      .btnFindAll.Enabled = True
      .btnAdd.Enabled = False
      .btnClone.Enabled = False
      .btnAlter.Enabled = False
      .btnOK.Enabled = False
    End With
  ElseIf Me.lstAnzeige.ListIndex > 0 Then    'User has selected
   intListIndex = Me.lstAnzeige.ListIndex
    With Sheet1
      Set rngSuchbereich = .Range(.Cells(2, cstrSCHLUESSEL).Address, .Cells(Rows.Count, cstrSCHLUESSEL).End(xlUp).Address)
    End With
    With rngSuchbereich
      Set rng = .Find(CStr(lstAnzeige.List(intListIndex, 9)), After:=rngSuchbereich.Cells(rngSuchbereich.Cells.Count), LookIn:=xlValues)
    End With
    With Me
      m_bln_PRUEFEN = True
      .frmAnsprechpartner.Visible = True
      For lngCounter = 1 To 9
        With .Controls("Textbox" & Format(lngCounter, "00"))
          .Enabled = True
          .BackColor = vbWhite
          .Value = Sheet1.Cells(rng.Row, lngCounter).Value
        End With
      Next lngCounter
      m_bln_PRUEFEN = False
      .cmbAngabe.Clear
      Select Case rng.Offset(, 1).Value
        Case "deutsch"
            For lngCmb = 2 To 5
                .cmbAngabe.AddItem Zentraleingabe.Cells(lngCmb, "H").Value
            Next lngCmb
        Case "englisch"
            For lngCmb = 2 To 5
                .cmbAngabe.AddItem Zentraleingabe.Cells(lngCmb, "J").Value
            Next lngCmb
      End Select
      If Len(lstAnzeige.List(intListIndex, 8)) > 0 Then
        .cmbAngabe.Value = rng.Offset(, 3).Value
        .txtName1.Value = vbNullString
        .txtName1.Value = rng.Offset(, 4).Value
        .txtName2.Value = vbNullString
        .txtName2.Value = rng.Offset(, 5).Value
        .txtName3.Value = vbNullString
        .txtName3.Value = rng.Offset(, 6).Value
      Else
        If Len(rng.Offset(0, 2).Value) > 0 Then
          .cmbAngabe.Value = "Zusatz"
         lblVorname.Caption = "Vorname"
        Else
          .cmbAngabe.Value = "Buchhaltung"
        End If
        .txtName1.Value = vbNullString
        .txtName2.Value = vbNullString
        .txtName3.Value = vbNullString
      End If
      .TextBox10.Value = lstAnzeige.List(intListIndex, 9)
      .btnFindAll.Enabled = True
      .btnAdd.Enabled = False
      .btnClone.Enabled = True
      .btnAlter.Enabled = True
      .btnOK.Enabled = True
    End With
  End If

End Sub

Ciao,
Holger

 

[Russmeister57]

Hi Holger,

Thank you for your feedback, i changed my code to match yours as yours was a lot less messy than mine but it still came up with a Run time error as you pointed out when an exact match was not achieved.
To solve this, i added in an Error Block and it now works perfectly.
Thank you for your assistance.
I have posted complete code below for anybody else to use.

Private Sub Search_CDetails_Click()

Dim varRet As Variant
On Error GoTo ErrHandler
If TextBox1.Value = "" Then
    MsgBox "    No Data Has Been Selected!", vbExclamation, "Nothing To Do Here"
   
Else
    varRet = Application.WorksheetFunction.Match(TextBox1, Sheets("TM Ownership").Range("Full_Name"), 0)
    If Not IsError(varRet) Then
   
        Sheets("data").Range("G5").Value = varRet
        With Sheet6.Range("TM_Ownership_Start")
            Colleague_Details.TextBox2 = .Offset(varRet, 1).Value
            Colleague_Details.TextBox4 = .Offset(varRet, 2).Value
            Colleague_Details.ComboBox3 = .Offset(varRet, 3).Value
            Colleague_Details.ComboBox1 = .Offset(varRet, 4).Value
            Colleague_Details.ComboBox4 = .Offset(varRet, 5).Value
            Colleague_Details.TextBox3 = .Offset(varRet, 6).Value
            Colleague_Details.TextBox5 = .Offset(varRet, 8).Value
            Colleague_Details.TextBox6 = .Offset(varRet, 9).Value
            Colleague_Details.TextBox7 = .Offset(varRet, 10).Value
            Colleague_Details.TextBox8 = .Offset(varRet, 11).Value
            Colleague_Details.TextBox9 = .Offset(varRet, 12).Value
            Colleague_Details.TextBox10 = .Offset(varRet, 13).Value
            Colleague_Details.CheckBox1 = .Offset(varRet, 34).Value
        End With
    Else
        MsgBox "No Data Found For: " & TextBox1.Value & "!", vbInformation, "No Data Available"
ErrHandler:
    MsgBox "This Colleague Isnt In The Database!", vbInformation, "No Colleague Data!"
    End If
End If

End Sub

 

[Peter_SSs]

@Russmeister57
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)