Help with Userform MsgBox

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
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:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,687
Office Version
2007
Platform
Windows
Try this

I assume the search column is A

Code:
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

Board Regular
Joined
Jan 23, 2016
Messages
170
Thanks for your reply DanteAmor,

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

Code:
.ScreenUpdating = False
Not sure why
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,687
Office Version
2007
Platform
Windows
I didn't modify that part of your code, but use the following:

Code:
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

Board Regular
Joined
Jan 23, 2016
Messages
170
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,687
Office Version
2007
Platform
Windows
Glad to help you, thanks for the feedback.
 

Forum statistics

Threads
1,085,670
Messages
5,385,103
Members
401,934
Latest member
cwinkel

Some videos you may like

This Week's Hot Topics

Top