Newbie and need help - assume zero knowledge! JLatham helped me with
this code, but don't know how to repeat it's function at
CommandButton3. Have read the VBA help and still none the wiser!
Would be very grateful if one of you guys would wave your magic
wands! Here's the code:
A BIG THANKYOU IN ADVANCE
Jeanette

Private Sub CommandButton1_Click()
'the "search" button will look for last name entries first
'if match is found, stops completely
'if no match found, checks to see if there's an entry in first name
box and if there is, will search for match

CommandButton1.Visible = True

Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String


'get the surname entry
'remove leading/trailing whitespace and convert to UPPERCASE for
tests

findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'we do have a last name to seek
'look in column B for it
Set searchList = ActiveSheet.Range("B1:" & _
ActiveSheet.Range("B" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
CommandButton1.Visible = False
CommandButton3.Visible = True
Exit Sub ' all finished
End If
End If
Next
'if we get here, no match on lastname was found, see if we have a
first name to look for
findEntry = UCase(Trim(Me.TextBox1))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
CommandButton1.Visible = False
CommandButton3.Visible = True
Exit Sub ' all finished
End If
End If
Next
End If
Else
'did not have last name, do we have
'a first name to search for
findEntry = UCase(Trim(Me.TextBox2))
If findEntry < "" Then
'have a first name to look for
'look in column C for it
Set searchList = ActiveSheet.Range("C1:" & _
ActiveSheet.Range("C" & Rows.Count).End(xlUp). _
Offset(1, 0).Address)
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = findEntry Then
'name matched, check formatting
If anyEntry.Font.ColorIndex < whatColor And _
anyEntry.Font.Strikethrough = False Then
anyEntry.Activate
Set searchList = Nothing
CommandButton1.Visible = False
CommandButton3.Visible = True
Exit Sub ' all finished
End If
End If
Next
End If
End If
End Sub
Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub
Private Sub CommandButton3_Click()
'Do exactly the same again, but start it from the active cell
End Sub
Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub