Populate USerform based on search query

pan_kaj

Board Regular
Joined
Dec 28, 2004
Messages
80
I have a userform that I use to enter fault calls and it saves in to a spreadsheet.

I want to develop the capability to be able to have a form that Will search a pre-existing call and then when it finds it to bring up the main userform and populate it with existing details so that the user can amend them if reqd and save back to the database.

So what i want.

Userform - New Call
Userform - search existing call

I want to open 'search existing call' userform, be able to search based on one of the following: callrefnum, fault_type, Call_logged_by
Once the call is found - it should be displayed and then on double click it shd open up in the main userform 'New Call' with all existing details populated.

Any help or reference to a thread which answers this will be appreciated.

Thanks!
PJ
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi,

Are you using Excel to store your data, or an external database?

If Excel, what is the format of the input data on your s/sheet?
 
Upvote 0
Hi,

ok, what is in what column, and are "callrefnum, fault_type, Call_logged_by" headings in row 1?
 
Upvote 0
Alan,

Columns are as follows: (in this order)

Name of Helpdesk Representative
Source
Mode
Fault Category
Fault Type
Service Vehicle Number
Head Code
Name of Caller
Contact details for Caller
Fault Descripton
Torex Call reference number
Time Logged
Date Logged
Call Status
Time updated
Date Update
Call history details
callRefNum



hope this helps...! thanks for helping out!
 
Upvote 0
Hi,

For the first part, try the following. This will select the searched for cell.

Create a userform & insert the following controls:

- Frame1
- 'OptionButton1', set the caption as appropriate and the Tag property to the CallRefNum column
- 'OptionButton2', set the caption as appropriate and the Tag property to the Fault_Type column
- 'OptionButton3', set the caption as appropriate and the Tag property to the Call_Logged_By column
- 'TextBox1' text box (to enter search text)
- A search button named 'btnSearch'
- an exit button named 'btnExit'

Insert the following into the Userform code window:
Code:
Option Explicit

Private Sub btnExit_Click()
Unload Me
End Sub

Private Sub btnSearch_Click()
Dim Ctl As Control
Dim lFindRow As Long
Dim sFindColumn As String

sFindColumn = ""
For Each Ctl In Frame1.Controls
    If LCase$(Left$(Ctl.Name, 3)) = "opt" Then
        If Ctl.Value = True Then
            sFindColumn = Ctl.Tag
            Exit For
        End If
    End If
Next Ctl

lFindRow = 0
If sFindColumn <> "" Then
    On Error Resume Next
    lFindRow = WorksheetFunction.Match(TextBox1.Text, _
                                        ActiveSheet.Columns(sFindColumn), _
                                        0)
    On Error GoTo 0
End If
If lFindRow <> 0 Then
    Cells(lFindRow, sFindColumn).Select
End If

Unload Me
End Sub

Private Sub TextBox1_Change()
btnSearch.Enabled = TextBox1.Text <> ""
End Sub

Private Sub UserForm_Activate()
btnSearch.Enabled = False
End Sub
 
Upvote 0
thanks Alan, this is now done. All i now need is to somehow get the results up and then once selected it to come up in the main userform with exissting data populated.

Thanks a ton mate!
 
Upvote 0
Hi, The Worksheet_BeforeDoubleClick event should do you to place the Userform Show statement.

The tricky part is to determine which data goes in which textbox. Maybe if you have your textboxes and Labels named 'TextBox1, Label1, TextBox2, Label2 etc with the number matching the data column in your database sheet, you could use something like this:
Code:
Private Sub UserForm_Activate()
Dim Ctl As Control
Dim iCol As Integer

For Each Ctl In Me.Controls
    If Left$(Ctl.Name, 7) = "TextBox" Then
        iCol = Val(Mid$(Ctl.Name, 8))
        Me.Controls("Label" & iCol).Caption = ActiveSheet.Cells(1, iCol).Value
        Ctl.Text = ActiveSheet.Cells(Selection.Row, iCol).Value
    End If
Next Ctl
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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