Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find & FindNext in a form
Hi, Can someone please help me here - trying to get Find and FindNext to work within to populate data within a form to enable it to be edited. Private Sub CommandButton1_Click() Dim Loc As String Loc = Format(TextBox1.Value, "####") Sheets("Sheet1").Select Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate With Worksheets("Sheet1") Set C = .Range("A:A").Find(Loc, LookIn:=xlValues) If Not C Is Nothing And .Cells(C.Row, 1) < Loc Then firstAddress = C.Address Do Set C = .Range("A:A").FindNext(C) Loop While Not C Is Nothing And .Cells(C.Row, 1) < Loc And C.Address < firstAddress TextBox2 = .Cells(C.Row, 2) TextBox3 = .Cells(C.Row, 3) End With End Sub Cheers, Bernz -- BernzG ------------------------------------------------------------------------ BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949 View this thread: http://www.excelforum.com/showthread...hreadid=395711 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find & FindNext in a form
Add a listbox (defaults to listbox1) and add the code below to the userform
code, replacing your code. I can see that you have been reading help, but had a few issues. First, you loop through the cells using find, but you try to populate text boxes at the end, after the loop. The code I wrote populates a list box during the loop. What you need to add is a listbox click event that will populate the two text boxes Option Explicit Private Sub CommandButton1_Click() Dim Loc As String Dim found As Range Loc = Format(TextBox1.Value, "####") ListBox1.Clear With Worksheets("Sheet1") Set found = .Cells.Find(What:=Loc) If Not found Is Nothing Then Loc = found.Address Do ListBox1.AddItem .Cells(found.Row, 2) ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row, 3) Set found = .Cells.FindNext(found) Loop While found.Address < Loc End If End With End Sub Private Sub ListBox1_Click() With ListBox1 TextBox2 = .List(.ListIndex, 0) TextBox3 = .List(.ListIndex, 1) End With End Sub Private Sub UserForm_Initialize() ListBox1.ColumnCount = 2 End Sub "BernzG" wrote: Hi, Can someone please help me here - trying to get Find and FindNext to work within to populate data within a form to enable it to be edited. Private Sub CommandButton1_Click() Dim Loc As String Loc = Format(TextBox1.Value, "####") Sheets("Sheet1").Select Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate With Worksheets("Sheet1") Set C = .Range("A:A").Find(Loc, LookIn:=xlValues) If Not C Is Nothing And .Cells(C.Row, 1) < Loc Then firstAddress = C.Address Do Set C = .Range("A:A").FindNext(C) Loop While Not C Is Nothing And .Cells(C.Row, 1) < Loc And C.Address < firstAddress TextBox2 = .Cells(C.Row, 2) TextBox3 = .Cells(C.Row, 3) End With End Sub Cheers, Bernz -- BernzG ------------------------------------------------------------------------ BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949 View this thread: http://www.excelforum.com/showthread...hreadid=395711 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find & FindNext in a form
Hi Patrick, Thanks for this tried it and it works okay. Have now modified it slightly to show the list box results afte textbox1 has been updated. Once data has been entered into textbox1 textbox1 is hidden and the listbox now appears with all the record with the same ID as in textbox1. You can select an entry in th listbox and the data in TextBox2 & 3 are updated. Cheers, Bernz Private Sub TextBox1_AfterUpdate() Dim Loc As String Loc = Format(TextBox1.Value, "####") Sheets("Sheet1").Select Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt: _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate With Worksheets("Sheet1") Set c = .Range("B:B").Find(Loc, LookIn:=xlValues) TextBox2 = .Cells(c.Row, 3) TextBox3 = .Cells(c.Row, 4) End With ListBox1.Visible = True TextBox1.Visible = False ListBox1.Clear With Worksheets("Sheet1") Set found = .Cells.Find(What:=Loc) If Not found Is Nothing Then Loc = found.Address Do ListBox1.AddItem .Cells(found.Row, 3) ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row, 4) Set found = .Cells.FindNext(found) Loop While found.Address < Loc End If End With End Sub Private Sub ListBox1_Click() With ListBox1 TextBox2 = .List(.ListIndex, 0) TextBox3 = .List(.ListIndex, 1) End With End Sub Private Sub UserForm1_Initialize() ListBox1.ColumnCount = 2 End Su -- Bernz ----------------------------------------------------------------------- BernzG's Profile: http://www.excelforum.com/member.php...fo&userid=2294 View this thread: http://www.excelforum.com/showthread.php?threadid=39571 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find & FindNext in a form
Hi Patrick. Well have come across another problem when using the listbox. Have extended the size of my database and now I get the following error message “ Runtime Error 380 – Could not set the list property. Invalid property value.” Private Sub UserForm1_Initialize() ListBox1.ColumnCount = 18 End Sub EXTRACT from macro in Userform Loc = TextBox1.Value ListBox1.Clear With Worksheets("Dbase") Set found = .Cells.Find(What:=Loc) If Not found Is Nothing Then Loc = found.Address Do ListBox1.AddItem .Cells(found.Row, 4) ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row, 2) ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(found.Row, 3) ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(found.Row, 5) ListBox1.List(ListBox1.ListCount - 1, 4) = .Cells(found.Row, 6) ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(found.Row, 7) ListBox1.List(ListBox1.ListCount - 1, 6) = .Cells(found.Row, 8) ListBox1.List(ListBox1.ListCount - 1, 7) = .Cells(found.Row, 9) ListBox1.List(ListBox1.ListCount - 1, 8) = .Cells(found.Row, 10) ListBox1.List(ListBox1.ListCount - 1, 9) = .Cells(found.Row, 11) ListBox1.List(ListBox1.ListCount - 1, 10) = .Cells(found.Row, 12) “Debug error message here” ListBox1.List(ListBox1.ListCount - 1, 11) = .Cells(found.Row, 13) ListBox1.List(ListBox1.ListCount - 1, 12) = .Cells(found.Row, 14) ListBox1.List(ListBox1.ListCount - 1, 13) = .Cells(found.Row, 15) ListBox1.List(ListBox1.ListCount - 1, 14) = .Cells(found.Row, 16) ListBox1.List(ListBox1.ListCount - 1, 15) = .Cells(found.Row, 17) Set found = .Cells.FindNext(found) Loop While found.Address < Loc End If End With Can't understand. look forward to hearing from you. Cheers, Bernz -- BernzG ------------------------------------------------------------------------ BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949 View this thread: http://www.excelforum.com/showthread...hreadid=395711 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting Find and FindNext | Excel Programming | |||
Using 'Find' and 'FindNext' in vba | Excel Programming | |||
Find Findnext in selected range | Excel Programming | |||
Find, Findnext VBA Loop | Excel Programming | |||
Find...FindNext Problem | Excel Programming |