Search Userform in Excel

sukyb1

Board Regular
Joined
Mar 27, 2009
Messages
153
HELP....im trying to create a excel sheet which does the following
I have the following colums...ProjNo, ProjName, EstimateNo, Estimator, Date Archived...I want a command button which once clicked brings up a useform
I want to add a userform which a user can type in the ProjNo and EstimateNo and in a box the Date Archived with automatically show up...
How do i do this?
<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
When the search button is clicked a useform comes up.
in the userform you have to type the estimate no and project no and then click search, on clicking search it should look for the estimate number and column number in the same row, if a match is found then the date or archive is put in label 4 and if nothing is found then label 4 should be blank.
Both ProjNo and EstimateNo have to be typed in.

Can anyone help with the coding?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi, try this.

UserForm1 contains the following controls:

TextBox - txtProjNo
TextBox - txtEstimateNo
Label - lblDateArchived
CommandButton - cmdSearch

Put this code in the UserForm1 module:
Code:
Option Explicit

Private Sub btnSearch_Click()

    Dim findProjNo As Range
    Dim sFirstAddress As String
    Dim bFound As Boolean
    
    bFound = False
    
    With Worksheets("Sheet1").Columns("A:A")
    
        'Look for ProjNo in Column A
        
        Set findProjNo = .Find(What:=Me.txtProjNo.Value, LookIn:=xlValues)
        
        If Not findProjNo Is Nothing Then
        
            'Check for matching EstimateNo 2 columns to the right of column A (column C)
            
            If CStr(findProjNo.Offset(0, 2).Value) = Me.txtEstimateNo Then
                bFound = True
            Else
                
                'Look for ProjNo and EstimateNo again
                
                sFirstAddress = findProjNo.Address
                Do
                    Set findProjNo = .FindNext(findProjNo)
                    If CStr(findProjNo.Offset(0, 2).Value) = Me.txtEstimateNo Then bFound = True
                Loop Until bFound Or findProjNo.Address = sFirstAddress
            End If
        End If

    End With
    
    If bFound Then
    
        'Populate form with DateArchived value from found row, 4 columns to the right of column A (column E)
        
        Me.lblDateArchived.Caption = findProjNo.Offset(0, 4).Value
    Else
        Me.lblDateArchived.Caption = ""
    End If
    
End Sub
Above code assumes ProjNo, ProjName, EstimateNo, Estimator, Date Archived are in columns A-E respectively. Adjust the code if not.

Put a Command Button from the Controls Toolbox on Sheet1. To have it display the Userform when clicked put this in the Sheet1 module:
Code:
Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub
 
Upvote 0
Thats great John,
I need to adapt it further though.
If someone only types in the project number I need it to show all the estimates that are under that project in a listbox (each project has many estimates, each estimate has a unique id)
from this listbox they can select the estimate they need, on clicking on this estimate in the listbox it fills the txtEstimateNo on the Useform

Can you help please?
 
Upvote 0
Try this. The userform now has the following additional control:

ListBox - lstEstimateNos

This code replaces the previously posted code.

Code:
Option Explicit

Private Sub btnSearch_Click()

    If txtProjNo <> "" Then
        If txtEstimateNo <> "" Then
        
            'Get DateArchived of record matching ProjNo and EstimateNo and put it on userform
            
            Get_DateArchived txtProjNo, txtEstimateNo
            
        Else
        
            'Fill userform Listbox with EstimateNos for ProjNo
            
            Fill_EstimateNos_Listbox txtProjNo
            
        End If
    End If
   
End Sub


Private Sub Get_DateArchived(sProjNo, sEstimateNo)

    'Search Sheet1 for first occurrence of specified ProjNo in column A and EstimateNo in column C.  If found,
    'populate lblDateArchived label on userform with corresponding DateArchived value from column E.
    
    Dim findProjNo As Range
    Dim sFirstAddress As String
    Dim bFound As Boolean

    With Worksheets("Sheet1").Columns("A:A")
    
        'Look for ProjNo in Column A
        
        Set findProjNo = .Find(What:=sProjNo, LookIn:=xlValues)
        
        If Not findProjNo Is Nothing Then
        
            'Check for matching EstimateNo 2 columns to the right of column A (column C)
            
            If CStr(findProjNo.Offset(0, 2).Value) = sEstimateNo Then
                bFound = True
            Else
                
                'Look for ProjNo and matching EstimateNo again
                
                sFirstAddress = findProjNo.Address
                Do
                    Set findProjNo = .FindNext(findProjNo)
                    If CStr(findProjNo.Offset(0, 2).Value) = sEstimateNo Then bFound = True
                Loop Until bFound Or findProjNo.Address = sFirstAddress
            End If
        End If

    End With
    
    If bFound Then
    
        'Get DateArchived value from same row, but 4 columns to the right of column A (column E)

        lblDateArchived.Caption = findProjNo.Offset(0, 4).Value
    Else
        lblDateArchived.Caption = ""
    End If

End Sub


Private Sub Fill_EstimateNos_Listbox(sProjNo As String)

    'Search Sheet1 for all occurrences of specified ProjNo in column A and populate lstEstimateNos listbox on
    'userform with corresponding EstimateNos
    
    Dim findProjNo As Range
    Dim sFirstAddress As String

    lstEstimateNos.Clear
    
    With Worksheets("Sheet1").Columns("A:A")
    
        'Look for ProjNo in column A
        
        Set findProjNo = .Find(What:=sProjNo, LookIn:=xlValues)
        
        If Not findProjNo Is Nothing Then
        
            'Found first occurrence.  Populate Listbox with corresponding EstimateNo 2 columns to the
            'right of column A (column C) and continue searching until back at the first occurrence
                
            sFirstAddress = findProjNo.Address
            Do
                lstEstimateNos.AddItem findProjNo.Offset(0, 2).Value
                Set findProjNo = .FindNext(findProjNo)
            Loop While Not findProjNo Is Nothing And findProjNo.Address <> sFirstAddress
        End If

    End With
    
End Sub


Private Sub lstEstimateNos_Click()
    txtEstimateNo = lstEstimateNos.Value
End Sub
 
Upvote 0
Hi John,
The userform has changed I need further help. Please ignore previous message.
I have an excel sheet with the following colums.
Asset; ProjNo; ProjName; EstNo, EstName; Estimator; Validated By, Date Archived

I want a userform where Estimator is a dropdown list, the user picks a estimator and all the estimates from that estimator are put in a listbox.
this listbox needs to hold all the other data fields (
Asset; ProjNo; ProjName; EstNo, EstName; Validated By, Date Archived)

The user can then select a project from the listbox and on double clicking a certain project labels within the form are filled with the relevant information for example:
<TABLE style="WIDTH: 738pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=982 border=0 x:str><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6656" width=182><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=88 height=20>Asset</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=111>Project No.</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=172>Project Name</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>Estimate No.</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 137pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=182>Estimate Name</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>Estimator</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=118>Validated By</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=115>Date of Archive</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>08 - Enhancements</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">000111</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Settle to Carilisle Capacity</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">07EN0129</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Installation of IB Signals</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Peter Gannon</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Iain Taylor</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="39895">23/03/2009</TD></TR></TBODY></TABLE>

If the above was clicked on in the usefrom then the labels are filled like this

lblAsset= 08 - Enhancement
lblProjNo = 000111
lbl ProjName = Settle to Carilisle Capacity
etc

Can you help?? Please.
Many Thanks
 
Upvote 0
Well, you've moved the goal posts even more, but try this :). It took me a while to develop this code as I was playing around with filters and listbox rowsources as the fastest way of putting data in the combobox and listbox. These techniques should be much faster than doing it row by row.

Also, I tried to populate the listbox from the combobox's Change event like this:

Code:
Private Sub cboEstimator_Change()
    Fill_Estimates_ListBox (cboEstimator.Value)
End Sub

but the autofilter returns an error. I tried numerous variations of Fill_Estimates_ListBox() but none worked. Instead, the user has to choose an Estimator from the combobox and then click the command button to populate the listbox.

The UserForm1 has the following controls on it:

ComboBox - cboEstimator
CommandButton - btnShowEstimates
ListBox - lstEstimates
Labels - lblAsset, lblProjectNo, lblProjectName, lblEstimateNo, lblEstimateName, lblEstimator, lblValidatedBy, lblDateArchived
Plus other labels as fixed text labels.

The code assumes that your data is on a sheet called Sheet1. It uses a sheet called Sheet2 to store temporary filtered data and this sheet must exist. These sheet names are defined at the top of the code in the Const statements where they can be easily changed.

Here is the code. It goes in the UserForm1 module.
Code:
Option Explicit

Const cDataSheetName As String = "Sheet1"
Const cTemporarySheetName As String = "Sheet2"

Public dataSheet As Worksheet
Public temporarySheet As Worksheet
Public lastDataRow As Long
Public firstFilteredCopyRow As Long, lastFilteredCopyRow As Long


Private Sub UserForm_Initialize()
    
    'Copy data to temporary sheet where it will provide a list of unique Estimators for the cboEstimators combobox and
    'filtered data for the lstEstimates listbox
    
    Dim lastRowColA As Long
        
    Set dataSheet = Sheets(cDataSheetName)
    Set temporarySheet = Sheets(cTemporarySheetName)
    
    firstFilteredCopyRow = 0
    
    'Determine the last row containing data
    
    With dataSheet
        lastDataRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    With temporarySheet
        
        'Clear temporary data
        
        .AutoFilterMode = False
        .Cells.ClearContents
        
        'Filter unique Estimators on data sheet column F and copy list to temporary sheet column A
        
        dataSheet.Range("F1:F" & lastDataRow).EntireColumn.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), Unique:=True
        
        'Get the number of unique Estimators and populate the cboEstimator combobox.  The combobox's RowSource
        'starts at A2 so that it doesn't include the column header
        
        lastRowColA = .Cells(.Rows.Count, "A").End(xlUp).Row
        cboEstimator.RowSource = temporarySheet.Name & "!" & .Range("A2:F" & lastRowColA).Address
    
        'Copy data sheet columns A:H to temporary sheet columns B:I where it will be filtered to provide values for
        'the lstEstimators listbox
    
        dataSheet.Range("A1:H" & lastDataRow).Copy Destination:=.Range("B1")
    
    End With
    
End Sub


Private Sub btnShowEstimates_Click()
    If cboEstimator.ListIndex >= 0 Then
        Fill_Estimates_ListBox (cboEstimator.Value)
    End If
End Sub


Private Sub Fill_Estimates_ListBox(sEstimator As String)

    'Populate the Estimates listbox with records matching the specified Estimator.  This is done by filtering data on
    'the temporary sheet and copying the visible rows so that a contiguous set of rows is available for
    'the listbox's RowSource property
    
    Dim filterRange As Range
    Dim lastRow As Long
    Dim visibleRows As Long
    Dim filteredCopyRowSource As String
    
    'Clear the listbox
    
    lstEstimates.RowSource = ""
    lstEstimates.Clear
            
    'Clear the copy of the filtered data that was last used to populate the listbox
    
    If firstFilteredCopyRow <> 0 Then
        temporarySheet.Rows(firstFilteredCopyRow & ":" & lastFilteredCopyRow).ClearContents
    End If
    
    With temporarySheet
        
        .AutoFilterMode = False
        
        'Create an autofilter of columns B:I filtered on the 6th column (column G) matching the specified Estimator
        
        lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set filterRange = .Range("B1:I" & lastRow)
        filterRange.AutoFilter Field:=6, Criteria1:=sEstimator
                
        'Create a contiguous copy of the visible rows (including the column headers row) starting 2 rows below the temporary data
                
        visibleRows = filterRange.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
        firstFilteredCopyRow = lastRow + 2
        lastFilteredCopyRow = firstFilteredCopyRow + visibleRows - 1
        With .AutoFilter.Range
            .Resize(.Rows.Count).Copy (.Cells(firstFilteredCopyRow, 1))
        End With
        
        'Construct listbox's RowSource string.  This starts at the first row of actual data so that the listbox automatically
        'picks up the column headers from the row above
        
        filteredCopyRowSource = .Name & "!" & _
                                .Range("B" & firstFilteredCopyRow + 1 & _
                                           ":I" & lastFilteredCopyRow).Address
        
    End With
        
    'Put the data in the listbox.  Its 6th column (Estimator) is hidden by setting that column's width to 0
    
    lstEstimates.ColumnCount = 8
    lstEstimates.ColumnWidths = "50;50;50;50;50;0;50;50"
    lstEstimates.RowSource = filteredCopyRowSource
        
End Sub

   
Private Sub lstEstimates_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    'User has double-clicked a row in the Estimates listbox.  Populate labels on the userform with values from the listbox row

    Dim i As Long
    
    If Not Cancel Then
        With lstEstimates
            i = .ListIndex
            lblAsset.Caption = .List(i, 0)
            lblProjectNo.Caption = .List(i, 1)
            lblProjectName.Caption = .List(i, 2)
            lblEstimateNo.Caption = .List(i, 3)
            lblEstimateName.Caption = .List(i, 4)
            lblEstimator.Caption = .List(i, 5)
            lblValidatedBy.Caption = .List(i, 6)
            lblDateArchived.Caption = Format(.List(i, 7), "dd/mm/yyyy")
        End With
    End If
    
End Sub
 
Last edited:
Upvote 0
John i've put in the code into the excel sheet but the combo box will not show any estimators.
 
Upvote 0
Change:
Code:
        dataSheet.Range("F1:F" & lastDataRow).EntireColumn.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), Unique:=True

To:

Code:
        dataSheet.Range("F1:F" & lastDataRow).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=dataSheet.Range("F1:F" & lastDataRow), CopyToRange:=.Range("A1"), Unique:=True
My code assumes that your data fields (Asset, ProjNo, ProjName, EstNo, EstName, Estimator, Validated By, Date Archived) are in Sheet1 columns A to H respectively with column headings in row 1. Sheet2 must exist and must be empty when the userform is run.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top