Search User Form & Populating User Form From Command Button

[excelbiff]

Hi all, first time poster here.

I have a workbook which acts as a fault database for a particular piece of equipment. For compliacted reasons I wont go into, we cannot use Access, hence me using Excel.

My queries are below:

1) I need the workbook to have a search user form whereby the user can enter the job number and the contents of the particular row of that job number will populate a pre defined user form. The problem is I'm unsure of the code to use.

2) On each row of column A, I want a button which when pressed will again bring up a user form populated with the contents of that row. Will the code be similar, if not the same, as the code from query 1)

Thanks in advance

 

[excelbiff]

Apologies for the double post but I have made 'some' progress.

I have a code for a search function based on job numbers in column C.

However when I press search, it searches by row number and not job number. For instance, say I wanted to search for Job Number 12345 and it was on row number 17 the userform is then populated with the details from row number 12345 and not row 17.

How can I manipulate the code below to get it to search by job number?

Private Sub cmdSearch_Click()
   Dim rw As Long             'row the search value was found on
   Dim myVal As Long          'converted search value
 
   On Error Resume Next
      'validate user input
      If txtSearch.Value = "" Or _
         Not IsNumeric(CLng(txtSearch.Value)) Then
         MsgBox "Enter valid SIR number!"
         txtSearch.SetFocus
         Exit Sub
      End If
 
      'convert the TexBox value to  data type long and get row number
      myVal = CLng(txtSearch.Value)
      rw = GetRowNumber(myVal)
   On Error GoTo 0
   If rw = 0 Then
      MsgBox "SIR Not Found"
   Else
      Call PopulateTextBoxes(rw)
   End If
End Sub

 

[bertie]

You can replace the call to the GetRowNumber

rw = GetRowNumber(myVal)

with a simple Find routine, highlighted red below

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdSearch_Click()
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]             [COLOR=green]'row the search value was found on[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] myVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]          [COLOR=green]'converted search value[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range
 
      [COLOR=green]'validate user input[/COLOR]
      [COLOR=darkblue]If[/COLOR] txtSearch.Value = "" Or _
         [COLOR=darkblue]Not[/COLOR] IsNumeric(CLng(txtSearch.Value)) [COLOR=darkblue]Then[/COLOR]
         MsgBox "Enter valid SIR number!"
         txtSearch.SetFocus
         [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=green]'convert the TexBox value to  data type long and get row number[/COLOR]
      myVal = [COLOR=darkblue]CLng[/COLOR](txtSearch.Value)
 
[COLOR=red]   On Error Resume Next[/COLOR]
 
[COLOR=red]       With Sheets("Sheet2").Range("C:C")[/COLOR]
[COLOR=red]         Set cell = .Find(myVal, LookIn:=xlValues)[/COLOR]
[COLOR=red]         rw = cell.Row[/COLOR]
[COLOR=red]       End With[/COLOR]
[COLOR=red]   On Error GoTo 0[/COLOR]
 
   [COLOR=darkblue]If[/COLOR] rw = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "SIR Not Found"
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=darkblue]Call[/COLOR] PopulateTextBoxes(rw)
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
End [COLOR=darkblue]Sub[/COLOR]

 

[excelbiff]

Brilliant Bertie!

TVM

 

[Macklin]

Hi EXCELBIFF,

I am very new to excel. I bought some books to try and figure out some of the code and I am still having trouble. What I am trying to accomplish sounds very close to what you have done. Would you mind displaying the code for the populatetextboxes(rw) reference?

I have a list of room numbers in column "A". They are actually text like "101A". I want to enter one into a search box, click the box and then populate another userform with textboxes with the data from the other columns in the same row. There I will enter data and edit as needed.

Thanks for any help

 

[bertie]

Hi EXCELBIFF,

I am very new to excel. I bought some books to try and figure out some of the code and I am still having trouble. What I am trying to accomplish sounds very close to what you have done. Would you mind displaying the code for the populatetextboxes(rw) reference?

I have a list of room numbers in column "A". They are actually text like "101A". I want to enter one into a search box, click the box and then populate another userform with textboxes with the data from the other columns in the same row. There I will enter data and edit as needed.

Thanks for any help

Here is one outline for a possible solution.

[COLOR=darkblue]Sub[/COLOR] PopulateTextBoxes([COLOR=darkblue]ByVal[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
 
  [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
    TextBox1.Value = .Range("A" & rw).Value
    TextBox2.Value = .Range("B" & rw).Value
    TextBox3.Value = .Range("C" & rw).Value
    [COLOR=green]'etc[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Macklin]

Thank you very much. That really helped a lot. My excel project is really coming together.