E
excelnut1954
I'm going to try this again. Obviously I didn't explain this very
well the other day. I get hung up on details sometimes, and maybe the
reader gets mislead as a result.
What I want is to somehow attach a unique number to a record(s) found
via a Find sub shown near the bottom (TestFind_POCurrent ). This would
be done so that I can show that number in a user form when the user is
looking at the record. I want to have this for when there is more than
1 record found (user will enter a PO number he wants to view, and there
will sometimes be more than one record on the list with that PO
number).
I have the UserForm13 that will show all of the fields of the record
found. This user form has TextBox15 that shows how many of that PO
number was found. I have a Next, and Previous button on this user form
that will show the next record, and the previous record, if there is
more than one on the list.
What I want is to have a new textbox on this user form that will show
which record the user is looking at. Each record found would have a
sequential number assigned to it.
Example: the user enters in a PO number to find that record(s). Suppose
that there are 4 such records on the list. UserForm13 will come up,
showing the 1st record. Textbox15 shows the number 4, indicating that
there are 4 records with this PO number. The user clicks the Next
button, and the next record will be shown. Textbox15 still shows the
number 4, since its only showing how many total records with that PO
number there are.
This is as far as I've gotten.
What I want is to have another textbox that will show the number 1 when
the 1st record is showing, the number 2 when the 2nd record is showing,
etc. And, those numbers will stay with each record while this user form
is up. So that when he clicks the Previous button, or the Next button,
over and over, back and forth, through it all, the 1st record will
always show number 1 in this new textbox, the 2nd record will show
number 2 in the new texbox, etc.
The end result is that I can show that the user is looking at Record 1
of 4, or Record 3 of 4, etc. Remember, I have the "4' part of this
example already, in TextBox15.
Here is the macro that shows the total records found during a search.
This number shows up in TextBox15 of UserForm13.
This code is in UserForm13
'Counter for how many of this PO number there are on the list
'This goes in TextBox15
CountPO = Application.CountIf(Range("J:J"), FindPOVal)
TextBox15.Value = CountPO
Here is the macro to perform the initial Find.
These are the declaration statements for the sub below:
Public rngToSearch As Range
Public rngFound As Range
Public strFirst As String
Public FindPOVal As String
Sub TestFind_POCurrent()
Worksheets("Official List").Activate
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "This record was not found. Make sure you entered the
correct number." Unload UserForm12
UserForm12.Show
Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show
End If
End Sub
I also have subs to perform the FindNext, and FindPrevous commands,
which are executed via the Find and Previous buttons within UserForm13.
I'm not sure if the code I'm looking for would be put in
UserForm13, where I have the code that counts the total records found
(TextBox15 code shown above), or if it would be put in Sub
TestFind_POCurrent.
If anyone understands what I am looking for, and can help out, please
let me know.
I really appreciate all those who try to help.
Thanks,
J.O.
well the other day. I get hung up on details sometimes, and maybe the
reader gets mislead as a result.
What I want is to somehow attach a unique number to a record(s) found
via a Find sub shown near the bottom (TestFind_POCurrent ). This would
be done so that I can show that number in a user form when the user is
looking at the record. I want to have this for when there is more than
1 record found (user will enter a PO number he wants to view, and there
will sometimes be more than one record on the list with that PO
number).
I have the UserForm13 that will show all of the fields of the record
found. This user form has TextBox15 that shows how many of that PO
number was found. I have a Next, and Previous button on this user form
that will show the next record, and the previous record, if there is
more than one on the list.
What I want is to have a new textbox on this user form that will show
which record the user is looking at. Each record found would have a
sequential number assigned to it.
Example: the user enters in a PO number to find that record(s). Suppose
that there are 4 such records on the list. UserForm13 will come up,
showing the 1st record. Textbox15 shows the number 4, indicating that
there are 4 records with this PO number. The user clicks the Next
button, and the next record will be shown. Textbox15 still shows the
number 4, since its only showing how many total records with that PO
number there are.
This is as far as I've gotten.
What I want is to have another textbox that will show the number 1 when
the 1st record is showing, the number 2 when the 2nd record is showing,
etc. And, those numbers will stay with each record while this user form
is up. So that when he clicks the Previous button, or the Next button,
over and over, back and forth, through it all, the 1st record will
always show number 1 in this new textbox, the 2nd record will show
number 2 in the new texbox, etc.
The end result is that I can show that the user is looking at Record 1
of 4, or Record 3 of 4, etc. Remember, I have the "4' part of this
example already, in TextBox15.
Here is the macro that shows the total records found during a search.
This number shows up in TextBox15 of UserForm13.
This code is in UserForm13
'Counter for how many of this PO number there are on the list
'This goes in TextBox15
CountPO = Application.CountIf(Range("J:J"), FindPOVal)
TextBox15.Value = CountPO
Here is the macro to perform the initial Find.
These are the declaration statements for the sub below:
Public rngToSearch As Range
Public rngFound As Range
Public strFirst As String
Public FindPOVal As String
Sub TestFind_POCurrent()
Worksheets("Official List").Activate
Set rngToSearch = Sheets("Official List").Columns("J")
Set rngFound = rngToSearch.Find(What:=FindPOVal, _
LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then
MsgBox "This record was not found. Make sure you entered the
correct number." Unload UserForm12
UserForm12.Show
Else
strFirst = rngFound.Address
rngFound.Select
Unload UserForm12
UserForm13.Show
End If
End Sub
I also have subs to perform the FindNext, and FindPrevous commands,
which are executed via the Find and Previous buttons within UserForm13.
I'm not sure if the code I'm looking for would be put in
UserForm13, where I have the code that counts the total records found
(TextBox15 code shown above), or if it would be put in Sub
TestFind_POCurrent.
If anyone understands what I am looking for, and can help out, please
let me know.
I really appreciate all those who try to help.
Thanks,
J.O.