Help with Userform MsgBox

[Gregm66]

Hi everyone,

I have a userform that has 2 textboxes to add data to a worksheet, this all works fine although i would like it to also search a worksheet called "Members" for a matching value entered into my first textbox called "txtMemFirst" if no matching name is found then it needs to show a msgbox "Member not found!"

If a matching name is found then continue...

Please see code below so far:

Private Sub CmbAdd_Click()
   Dim rw As Long    'next available row
 
   With Sheets("Fees Paid")
   
        .ScreenUpdating = False
        .EnableEvents = False
 
      'get the next avialable row in Sheet1
      rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
 
      'put the text box values in this row
      .Range("B" & rw).Value = txtMemFirst.Value
      .Range("I" & rw).Value = txtAmountPaid.Value
      
        .EnableEvents = True
        .ScreenUpdating = True
 
   End With
 
   '================================
   'Clear the text boxes
   '================================
   txtMemFirst.Value = ""
   txtAmountPaid.Value = ""
End Sub

 

[DanteAmor]

Try this

I assume the search column is A

Private Sub CmbAdd_Click()
  Dim rw As Long    'next available row
  Dim f As Range
  If txtMemFirst = "" Then
    MsgBox "Enter a Member"
    txtMemFirst.SetFocus
    Exit Sub
  End If
  
  Set f = Sheets("Members").Range("[COLOR=#ff0000]A:A[/COLOR]").Find(txtMemFirst.Value, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Member not found!", vbExclamation, "MEMBERS"
    Exit Sub
  End If
  With Sheets("Fees Paid")
    .ScreenUpdating = False
    .EnableEvents = False
    'get the next avialable row in Sheet1
    rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    'put the text box values in this row
    .Range("B" & rw).Value = txtMemFirst.Value
    .Range("I" & rw).Value = txtAmountPaid.Value
    .EnableEvents = True
    .ScreenUpdating = True
  End With
  
  '================================
  'Clear the text boxes
  '================================
  txtMemFirst.Value = ""
  txtAmountPaid.Value = ""
End Sub

 

[Gregm66]

Thanks for your reply DanteAmor,

your code worked, except when i entered a valid name the code stopped at..

.ScreenUpdating = False

Not sure why

 

[DanteAmor]

I didn't modify that part of your code, but use the following:

Private Sub CmbAdd_Click()
  Dim rw As Long    'next available row
  Dim f As Range
  If txtMemFirst = "" Then
    MsgBox "Enter a Member"
    txtMemFirst.SetFocus
    Exit Sub
  End If
  
  Set f = Sheets("Members").Range("A:A").Find(txtMemFirst.Value, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Member not found!", vbExclamation, "MEMBERS"
    Exit Sub
  End If
  With Sheets("Fees Paid")
    [COLOR=#ff0000]Application[/COLOR].ScreenUpdating = False
    [COLOR=#ff0000]Application[/COLOR].EnableEvents = False
    'get the next avialable row in Sheet1
    rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    'put the text box values in this row
    .Range("B" & rw).Value = txtMemFirst.Value
    .Range("I" & rw).Value = txtAmountPaid.Value
    [COLOR=#ff0000]Application[/COLOR].EnableEvents = True
    [COLOR=#ff0000]Application[/COLOR].ScreenUpdating = True
  End With
  
  '================================
  'Clear the text boxes
  '================================
  txtMemFirst.Value = ""
  txtAmountPaid.Value = ""
End Sub

 

[Gregm66]

Thanks DanteAmor,

That works i although i had to Rem out those lines involving screenupdating and enable events as i should have mentioned i have other code working there to auto fill other cells, and by leaving those lines in it wont allow the auto fill other cells to work, no dramas though just lag thats all...

Thankyou for your help

 

[DanteAmor]

Glad to help you, thanks for the feedback.