I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. If one of you brilliant techies can help me, I'd be
ever grateful!!

Jeanette,
I don't know how much you know/don't know about user forms, so I'll go
through this in some detail and you can skip over the parts you don't need.
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created. For
that we need a regular subroutine. Open your workbook and press [Alt]+[F11]
to open the VB editor. In its menu, choose Insert -- Module and copy and
paste this code into it:
Sub OpenUserForm1()
UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert -- UserForm it should insert a
form named UserForm1 for you. Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name". Add a textbox next to
it, that text box's name will be TextBox1. Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons: put one on the form and set its text
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list. A module will open up with a 'stub' entry for
UserForm_Click() created. You don't need that. Just copy all the code below
and paste it over anything that the editor provided for you. Your userform
will be ready to use then. To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools -- Macro
-- Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use. Here's the code for the userform:


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
'
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
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
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
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 UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub

"Jeanette" wrote:

I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. If one of you brilliant techies can help me, I'd be
ever grateful!!

On 15 June, 00:03, JLatham wrote:
Jeanette,
I don't know how much you know/don't know about user forms, so I'llgo
through this in some detail and you can skip over the parts you don't need. *
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created. *For
that we need a regular subroutine. *Open your workbook and press [Alt]+[F11]
to open the VB editor. *In its menu, choose Insert -- Module and copy and
paste this code into it:
Sub OpenUserForm1()
* UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert -- UserForm *it should insert a
form named UserForm1 for you. *Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name". *Add a textbox next to
it, that text box's name will be TextBox1. *Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons: *put one on the form and set its text
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list. *A module will open up with a 'stub' entry for
UserForm_Click() created. *You don't need that. *Just copy all the code below
and paste it over anything that the editor provided for you. *Your userform
will be ready to use then. *To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools -- Macro
-- Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use. *Here's the code for the userform:

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
* '
* 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
* * * * * 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
* * * * * * 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
* * * * * * 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 UserForm_Initialize()
* Me.TextBox1 = "" ' last name box
* Me.TextBox2 = "" ' first name box
End Sub



"Jeanette" wrote:
I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. *If one of you brilliant techies can help me, I'd be
ever grateful!!- Hide quoted text -

- Show quoted text -

JLatham very kindly helped me out with this and I have replied in the
Group, but no-one is able to help me so I'm posting this again. I’m
hoping that someone could tweak this code so that I can keep
performing the same task until I find the entry I want (you see, some
people may have the same surname or the same firstname) – i.e., search
again but from the active cell/current position. Here’s how I need
for it to work:

1. Find some surname in column B that’s not red strikethrough
2. Go to that cell
3. (Found the surname but that’s not the one I want because the
firstname in column C isn’t the right match)
4. Find some surname again in column B that’s not red strikethrough
(but this time, search from the one I just found)
5. Go to that cell
6. Found the surname but that’s not the one I want either .... etc.,
etc. (keep repeating Nos. 4 and 5 with each click of the
CommandButton)

I did create the userform as instructed, and I’ve even managed to add
another CommandButton(3) that shows itself after the code has run. So
the ideal scenario would be to perform the repeated searches (Nos. 4
and 5 above) when you click that button. God, I hope this makes
sense! I know what I want to do, but haven’t got a clue how to get
there! So if anyone can help me, you’d be a star!

Thanks in anticipation.
Jeanette

Sorry to take so long, got tied up on some other stuff. Anyhow...

Replace the code I gave you before with the code before. You won't need
your 3rd button. Instead, when a match is found a prompt will come up asking
whether or not to continue searching. You simply respond by clicking the
[Yes] or [No] button in the prompt window.

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
'
Const whatColor = 3 ' 3=red
Dim searchList As Range
Dim anyEntry As Range
Dim findEntry As String
Dim whatToDoNow As Integer

'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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") < vbYes Then
Set searchList = Nothing
Exit Sub
End If
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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") < vbYes
Then
Set searchList = Nothing
Exit Sub
End If
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
Application.Goto anyEntry, True
anyEntry.Activate
'stop or continue searching for last name
If MsgBox("Continue searching?", vbYesNo, "What Now?") < vbYes
Then
Set searchList = Nothing
Exit Sub
End If
End If
End If
Next
End If
End If
End Sub

Private Sub CommandButton2_Click()
'the "cancel" button
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub




"Jeanette" wrote:

On 15 June, 00:03, JLatham wrote:
Jeanette,
I don't know how much you know/don't know about user forms, so I'llgo
through this in some detail and you can skip over the parts you don't need.
If there are parts you need more help with, just ask.

First, you'll need a way to open your userform once you get it created. For
that we need a regular subroutine. Open your workbook and press [Alt]+[F11]
to open the VB editor. In its menu, choose Insert -- Module and copy and
paste this code into it:
Sub OpenUserForm1()
UserForm1.Show
End Sub

Next, still in the VB editor, choose Insert -- UserForm it should insert a
form named UserForm1 for you. Use the toolbox to add a label and change the
text in it to something like "Surname" or "Last Name". Add a textbox next to
it, that text box's name will be TextBox1. Repeat this process to add
another label ("First Name" in it) and another text box that should be
TextBox2.

We're ready for the 2 command buttons: put one on the form and set its text
to something like "Search", and set the second one's text to something like
"Cancel/Close".

Right-click in a clear area anywhere on the userform and choose [View Code]
from the list. A module will open up with a 'stub' entry for
UserForm_Click() created. You don't need that. Just copy all the code below
and paste it over anything that the editor provided for you. Your userform
will be ready to use then. To try it all out, close the VB Editor and then
from the Excel worksheet with your list of names on it, use Tools -- Macro
-- Macros to highlight the OpenUserForm macro and use the [Run] button to
open it up for use. Here's the code for the userform:

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
'
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
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
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
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 UserForm_Initialize()
Me.TextBox1 = "" ' last name box
Me.TextBox2 = "" ' first name box
End Sub



"Jeanette" wrote:
I'd really appreciate some help...
I'd like to create a userform with two input boxes - one for Surname,
one for Firstname, two CommandButtons, one to perform action, the
other to cancel.
The action I want to perform is for the user to type a surname in
inputbox1 and then do a search in column B for that surname, that's
NOT red/strikethrough OR for the user to type a Firstname in inputbox2
and do a search for that firstname in column C that's NOT red/
strikethrough. If one of you brilliant techies can help me, I'd be
ever grateful!!- Hide quoted text -

- Show quoted text -

JLatham very kindly helped me out with this and I have replied in the
Group, but no-one is able to help me so I'm posting this again. Im
hoping that someone could tweak this code so that I can keep
performing the same task until I find the entry I want (you see, some
people may have the same surname or the same firstname) €“ i.e., search
again but from the active cell/current position. Heres how I need
for it to work:

1. Find some surname in column B thats not red strikethrough
2. Go to that cell
3. (Found the surname but thats not the one I want because the
firstname in column C isnt the right match)
4. Find some surname again in column B thats not red strikethrough
(but this time, search from the one I just found)
5. Go to that cell
6. Found the surname but thats not the one I want either .... etc.,
etc. (keep repeating Nos. 4 and 5 with each click of the
CommandButton)

I did create the userform as instructed, and Ive even managed to add
another CommandButton(3) that shows itself after the code has run. So
the ideal scenario would be to perform the repeated searches (Nos. 4
and 5 above) when you click that button. God, I hope this makes
sense! I know what I want to do, but havent got a clue how to get
there! So if anyone can help me, youd be a star!

Thanks in anticipation.
Jeanette