Populate Userform TextBox with search result

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
hi all, i'm hoping this is a relatively easy one...but it seems to have confused me no end.

I have built a UserForm (see picture) that is intended to allow users to search the entire workbook using a keyword placed in the provided search box - i.e. a user could enter any one of part number, serial number, registration code etc.

1614349370261.png


On clicking the 'search' button, I want the users search result(s) to populate the various text boxes with the relevant details found from the search row. Now, I have managed to disable and colour all the text boxes so they cannot be typed in, and on clicking the 'search' button these are re-enabled and coloured white. My next step is to complete the search code, and whilst i've had some success in the past with searching, that has only been for a value in a single column, not across multiple columns in all worksheets within the workbook.

Does anybody have any suggestions or possible solutions i could look into please??

Thanks in advance

Si
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
It would be hard to test a possible solution without having access to your userform and data. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
thanks Mumps... i can certainly try, however to de-sensitize it would mean removing practically all of my my data and replacing it with 'stuff'...

in essence, the userform textboxes are all named the same as the columns they refer to on the main worksheet (called 'demands'). I suppose i can limit the search to that sheet for now as all other sheets within the workbook contained various archived data that dosen't need to be searched in the grand scheme of things...
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
I wouldn't need all your data, just 4 or 5 rows (de-sensitized).
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

previously i wrote this code (below) and used it to display a single text box for search string entry, and then used that actually highlight the found row within the workbook:
VBA Code:
Sub Find_Button()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter your search:")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(what:=datatoFind, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.value = datatoFind Then Exit Sub
Next counter
If ActiveCell.value <> datatoFind Then
MsgBox ("The search returned no hits.")
Sheets(currentSheet).Activate
End If
End Sub

and elsewhere, i have used a listbox and userform combination as below to allow data entry into the textbox they have a reference for (e.g. part number in 'pt number', demand number in 'dmd num' etc)
1614352108014.png


which worked with this VBA:
VBA Code:
Private Sub DmdNo_Change()

Dim DmdVal As Long
On Error Resume Next

DmdVal = Me.DmdNo.Value

'clear ainu combobox
Me.AinU.Clear

'loop through dmd no's
lr = ThisWorkbook.Sheets("Master Sheet").Cells(Rows.Count, 7).End(xlUp).Row

'loop through to get AinU
For x = 9 To lr
    If DmdVal = ThisWorkbook.Sheets("Master Sheet").Cells(x, 7) Then
        'add to AinU combobox
       Me.AinU.AddItem ThisWorkbook.Sheets("Master Sheet").Cells(x, 10)
    End If
Next x

Me.AinU.ListIndex = 0

End Sub

Private Sub btnDemProg_Click()
  Dim sh As Worksheet, r As Range, f As Range, Cell As String, wRow As Long
  
  'clear the userform boxes
  nsc.Value = ""
  nc.Value = ""
  iin.Value = ""
  desc.Value = ""
  DmdDate.Value = ""
  AinUHasten.Value = ""
  PTHast1.Value = ""
  PTHast2.Value = ""
  PTHast3.Value = ""
  
  '
  If DmdNo = "" Then
    MsgBox "Demand Number is empty."
    DmdNo.SetFocus
    Exit Sub
  End If
  If AinU = "" Then
    MsgBox "Choose the demanding AinU."
    AinU.SetFocus
    Exit Sub
  End If
  
  Set sh = Sheets("Master Sheet")
  Set r = Sheets("Master Sheet").Range("G:G")
  Set f = r.Find(DmdNo.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Cell = f.Address
    Do
      If f.Offset(, 3).Value = AinU.Value Then
        wRow = f.Row
        Exit Do
      End If
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> Cell
  End If
  If wRow > 0 Then
    nsc.Value = f.Offset(, -5)
    nc.Value = f.Offset(, -4)
    iin.Value = f.Offset(, -3)
    desc.Value = f.Offset(, -2)
    DmdDate.Value = Format((f.Offset(, 1)), "DD-MMM-YY")
    AinUHasten.Value = Format((f.Offset(, 7)), "DD-MMM-YY")
    PTHast1.Value = Format((f.Offset(, 8)), "DD-MMM-YY")
    PTHast2.Value = Format((f.Offset(, 9)), "DD-MMM-YY")
    PTHast3.Value = Format((f.Offset(, 10)), "DD-MMM-YY")
    
    If f.Offset(, 11) = "" Then
        PTReply.Value = "No update"
        Else: PTReply.Value = f.Offset(, 11)
    End If
  Else
    MsgBox "I cannot find this demand. Has it been cancelled/satisfied?"
  End If
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
I'm sorry but it would be easier if you could upload a de-desensitized file of 4 or 5 rows of data rather than trying to decipher your code without being able to see your data or actual userform.
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

post removed
 
Last edited:

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
I'm sorry but it would be easier if you could upload a de-desensitized file of 4 or 5 rows of data rather than trying to decipher your code without being able to see your data or actual userform.
Hi Mumps, sorry for the delay...

I've re-designed the form to look like this:

1614874094410.png


The 'enabled' boxes (in white) are where I want the user to be able to input a search term. When any one of them has a input (end user may not have all details to hand), the combo boxes list all matching row values to the 'enabled' boxes (using a partial AND case-insensitive match - example: we could have multiple orders against one part number, so this allows the user to input upper and lower case text and select the correct order from all matching options.

The user then clicks the '>>click here<<' button at the top and it fills in the rest of the boxes and makes them all white.

as i'm unable to provide a de-sensitised speadsheet with the information, i've provided below the code locating the various columns required to complete the boxes... is this enough for you?

VBA Code:
       Me.DmdNo.value = ThisWorkbook.Sheets("DEMANDS").Cells( , 1)
       Me.DmdDate.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 2)
       Me.nsn.value = ThisWorkbook.Sheets("DEMANDS").Cells(,3)
       Me.PTNum.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 4)
       Me.desc.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 5)
       Me.qty.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 6)
       Me.DofQ.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 7)
       Me.RDD.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 8)
       Me.section.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 18)
       Me.POC.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 20)
       Me.ainu.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 21)
       Me.inv.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 22)
       Me.trilogy.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 17)
       Me.ACtailNo.value ThisWorkbook.Sheets("DEMANDS").Cells(, 16)
       Me.TechDoc.value = ThisWorkbook.Sheets("DEMANDS").Cells(,28)
       Me.higher.value = ThisWorkbook.Sheets("DEMANDS").Cells(,30)
       Me.ACSys.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 19)
       Me.ADF_LIM_Number.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 25)
       Me.SNOW.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 26)
       Me.reason.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 27)
       Me.ProgText.value = ThisWorkbook.Sheets("DEMANDS").Cells(, 31)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
I'm sorry but your link doesn't work and I don't think I will be able to help without seeing the sample file as requested in Post #6.
 

si3po

Board Regular
Joined
Jan 7, 2019
Messages
91
Office Version
  1. 365
Platform
  1. Windows
I'm sorry but your link doesn't work and I don't think I will be able to help without seeing the sample file as requested in Post #6.
sorry, now edited - im afriad that i can't provide a sample file as i've been told it would be a breach of our Cyber SOPs.

i was hoping that from the code above youd be able to see which columns contained which data required. The code shows all fields, top to bottom reading left to right in order of their location on the form itself.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,911
Messages
5,638,947
Members
417,060
Latest member
wcbobb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top