Populate USerform based on search query

[pan_kaj]

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

 

[pan_kaj]

Any help guys?

 

[pan_kaj]

still waiting!

 

[al_b_cnu]

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?

 

[pan_kaj]

am using excel 2003. data is stored in columns in text format.

 

[al_b_cnu]

Hi,

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

 

[pan_kaj]

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!

 

[al_b_cnu]

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:

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

 

[pan_kaj]

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!

 

[al_b_cnu]

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:

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