Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on FindPrevious command in a userform
I'v recently learned how to implement the Find, and FindNext commands
in some userforms I've designed. They search a list of PO numbers, and work ok. Below, I'll show the subs I have that use these commands. What I want to do now is to put in a FindPrevious button in these userforms. That way, the user can toggle each way in the list of found PO numbers. I tried to use the same concept of the FindNext, but it doesn't work. I didn't expect it to. I think I may want to re-design what I have to accomplish this better. I'm thinking that what I may want to do at the point where I do the initial Find for a PO number, is that any cell containing this PO number would get a "name" (variable name? Can't think of the term I want). Then, the FindNext would go to each of these names, and the FindPrevious would go back one record. I think if I can find out how to name all the cells that contain a PO number during this initial Find, then I might be able to figure out the rest, and how to insert this into the Find, FindNext, and FindPrevious commands. Here are the 2 subs I use the Find and FindNext commands. Sub FindViaPOCurrent() 'This is for the PO/PL search via UserForm12. Clicking the OK button 'brings you here. If record found, it opens up UserForm13 to show 'that record. The "Find Another Record" button will also loop back here. Worksheets("Official List").Activate Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=FindPOVal, _ LookIn:=xlValues) If rngFound Is Nothing Then MsgBox "This record was not found. Make sure you entered the correct number." Worksheets("Menu").Activate Unload UserForm12 UserForm12.Show Else strFirst = rngFound.Address rngFound.Select Unload UserForm12 UserForm13.Show End If End Sub Sub FindNextViaPOCurrent() 'This is routine from clicking the "Get the next record w/ same PO..." 'button. If no duplicates found, you get message. If there is, it 'brings up UserForm13 like above. Set rngFound = rngToSearch.FindNext(rngFound) If rngFound.Address = strFirst Then MsgBox "There are no other records with this PO/PL. Search for a different PO/PL, or click Close" Else rngFound.Select Unload UserForm13 UserForm13.Show End If End Sub I would appreciate any help with this. Thanks, J.O. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on FindPrevious command in a userform
You can create a range object of all of the found PO's if you wnat . That is
easy to do. Before we go their hwoever why exactly did FindPrevious not work? Without trying it myself I do not see a reason why it wouldn't work... -- HTH... Jim Thomlinson "excelnut1954" wrote: I'v recently learned how to implement the Find, and FindNext commands in some userforms I've designed. They search a list of PO numbers, and work ok. Below, I'll show the subs I have that use these commands. What I want to do now is to put in a FindPrevious button in these userforms. That way, the user can toggle each way in the list of found PO numbers. I tried to use the same concept of the FindNext, but it doesn't work. I didn't expect it to. I think I may want to re-design what I have to accomplish this better. I'm thinking that what I may want to do at the point where I do the initial Find for a PO number, is that any cell containing this PO number would get a "name" (variable name? Can't think of the term I want). Then, the FindNext would go to each of these names, and the FindPrevious would go back one record. I think if I can find out how to name all the cells that contain a PO number during this initial Find, then I might be able to figure out the rest, and how to insert this into the Find, FindNext, and FindPrevious commands. Here are the 2 subs I use the Find and FindNext commands. Sub FindViaPOCurrent() 'This is for the PO/PL search via UserForm12. Clicking the OK button 'brings you here. If record found, it opens up UserForm13 to show 'that record. The "Find Another Record" button will also loop back here. Worksheets("Official List").Activate Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=FindPOVal, _ LookIn:=xlValues) If rngFound Is Nothing Then MsgBox "This record was not found. Make sure you entered the correct number." Worksheets("Menu").Activate Unload UserForm12 UserForm12.Show Else strFirst = rngFound.Address rngFound.Select Unload UserForm12 UserForm13.Show End If End Sub Sub FindNextViaPOCurrent() 'This is routine from clicking the "Get the next record w/ same PO..." 'button. If no duplicates found, you get message. If there is, it 'brings up UserForm13 like above. Set rngFound = rngToSearch.FindNext(rngFound) If rngFound.Address = strFirst Then MsgBox "There are no other records with this PO/PL. Search for a different PO/PL, or click Close" Else rngFound.Select Unload UserForm13 UserForm13.Show End If End Sub I would appreciate any help with this. Thanks, J.O. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on FindPrevious command in a userform
This is close to what you want to do.. Note that there is now a rngcurrent in
each sub... Dim strFirst As String Dim rngToSearch As Range Dim rngFound As Range Private Sub CommandButton1_Click() Set rngToSearch = Sheet1.Columns("A") Set rngFound = rngToSearch.Find(What:="This", _ LookIn:=xlValues, _ LookAt:=xlWhole) If Not rngFound Is Nothing Then rngFound.Select strFirst = rngFound.Address End If End Sub Private Sub CommandButton2_Click() Dim rngCurrent As Range Set rngCurrent = rngToSearch.FindNext(rngFound) If rngCurrent.Address = strFirst Then 'disable find next button MsgBox "the end" Else 'enable find previous button Set rngFound = rngCurrent rngFound.Select End If End Sub Private Sub CommandButton3_Click() Dim rngCurrent As Range Set rngCurrent = rngToSearch.FindPrevious(rngFound) If rngCurrent.Address = strFirst Then 'disable find previous button MsgBox "the end" Else 'enable find next button Set rngFound = rngCurrent rngFound.Select End If End Sub -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: You can create a range object of all of the found PO's if you wnat . That is easy to do. Before we go their hwoever why exactly did FindPrevious not work? Without trying it myself I do not see a reason why it wouldn't work... -- HTH... Jim Thomlinson "excelnut1954" wrote: I'v recently learned how to implement the Find, and FindNext commands in some userforms I've designed. They search a list of PO numbers, and work ok. Below, I'll show the subs I have that use these commands. What I want to do now is to put in a FindPrevious button in these userforms. That way, the user can toggle each way in the list of found PO numbers. I tried to use the same concept of the FindNext, but it doesn't work. I didn't expect it to. I think I may want to re-design what I have to accomplish this better. I'm thinking that what I may want to do at the point where I do the initial Find for a PO number, is that any cell containing this PO number would get a "name" (variable name? Can't think of the term I want). Then, the FindNext would go to each of these names, and the FindPrevious would go back one record. I think if I can find out how to name all the cells that contain a PO number during this initial Find, then I might be able to figure out the rest, and how to insert this into the Find, FindNext, and FindPrevious commands. Here are the 2 subs I use the Find and FindNext commands. Sub FindViaPOCurrent() 'This is for the PO/PL search via UserForm12. Clicking the OK button 'brings you here. If record found, it opens up UserForm13 to show 'that record. The "Find Another Record" button will also loop back here. Worksheets("Official List").Activate Set rngToSearch = Sheets("Official List").Columns("J") Set rngFound = rngToSearch.Find(What:=FindPOVal, _ LookIn:=xlValues) If rngFound Is Nothing Then MsgBox "This record was not found. Make sure you entered the correct number." Worksheets("Menu").Activate Unload UserForm12 UserForm12.Show Else strFirst = rngFound.Address rngFound.Select Unload UserForm12 UserForm13.Show End If End Sub Sub FindNextViaPOCurrent() 'This is routine from clicking the "Get the next record w/ same PO..." 'button. If no duplicates found, you get message. If there is, it 'brings up UserForm13 like above. Set rngFound = rngToSearch.FindNext(rngFound) If rngFound.Address = strFirst Then MsgBox "There are no other records with this PO/PL. Search for a different PO/PL, or click Close" Else rngFound.Select Unload UserForm13 UserForm13.Show End If End Sub I would appreciate any help with this. Thanks, J.O. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on FindPrevious command in a userform
It was a sloppy attempt. I was kind of thinking of the concept I
described above. I just thought of putting in a previous button, and read the help on it. I thought I had to go a little deeper than just replacing variables in the 2 subs I had already. Range object. That's what I was thinking of. Thanks for the code below. I'll give it a try. J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform commnad from a sheet command | Excel Discussion (Misc queries) | |||
SUM command not working | Excel Worksheet Functions | |||
Specifying a Command Button on a worksheet as differentiated from one on a UserForm | Excel Programming | |||
Command for userform to load website link | Excel Programming | |||
Userform disappears when you try to initialize from a command button | Excel Programming |